Deploy SSIS Packages discussion

Topics: Resolved
Coordinator
Jun 18, 2008 at 5:30 PM
MatthewRoche wrote  Mar 5 at 9:03 PM 

1) I can't believe I've missed this until now - this is a very exciting direction for BIDS Helper!
2) In order for this feature to be truly useful (at least in my opinion) above and beyond the built-in deployment utility it needs to include the following functionality
A) Solution-level scope: Most complex ETL systems are built with multiple SSIS projects, and having the ability to deploy all projects in a solution is vital.
B) Hands-off mode: Deploying from BIDS is a nice developer tool, but it's not repeatable. What we really need is the ability to generate a scriptable and repeatable deployment utility that will allow the deployment process to be tested and managed as a first-class application artifact. This could be something as simple as a batch file that automates DTUTIL based on the project/solution settings, or something as involved as an XML deployment manifest that then gets consumed by a deployment application and contains all of the settings specified in the project/solution settings. The key thing here is that the deployment can be hands-of and repeatable.
C) Configuration support will be nice too. ;-)

Keep up the great work, guys!!
Coordinator
Jun 18, 2008 at 5:34 PM

To respond to your concerns, Matthew...

2A) So if we changed this to have a solution level menu option, my thoughts would be the following: a) the menu item would have to say "Deploy SSIS Packages" since your solution might have non-SSIS projects (b) the configurations which drive the deploy destination are per project (since SSIS configurations are per project) and (c) would the solution level menu option do anything other than run deploy for each project?

2B) Under the covers it just executes dtutil. So you should be able to create a bat file or something to automate this outside of BIDS fairly easily.

2C) We toyed with this, but there are about 1,000 permutations of how you can do configurations and deploy configurations. So we decided it would probably just cause more problems than it was worth. Plus, deploying configurations is usually just a one-time setup activity, where deploying packages is an ongoing activity every time you tweak something and want to run it from the server again.

Jun 18, 2008 at 6:14 PM
Let's see...

2A-a) Agreed, and understood.
2A-b) Configurations aren't really per-project artifacts; this is simply one way of using them. I have solutions with a dozen or more SSIS projects, and a dedicated "resources" project that doesn't contain any package files, but does contain all of the configuration files and other non-executable resources that are used by packages in all of the other projects.
2A-c) No, I don't think there would be any difference - running deploy for each project is really the only way this makes sense to me.

2B) Very cool - this makes things so much simpler. What do you think of giving the user the option to either execute DTUTIL (as per the current functionality) or save the DTUTIL calls to a batch file for future execution and/or modification? This would be the best of both worlds in my book.

2C) As a variation on the previous "create a batch file" option, why not also give the option of having that batch file include calling COPY to deploy the config files used by each package, and call SETX to create any environment variables used in any indirect configurations? This seems like an 80/20 effort to me, in that it will give the user a great head-start if he decides to use it, and not get in the way otherwise?
Coordinator
Jun 18, 2008 at 10:06 PM
2A-b) Sorry. I meant Visual Studio configurations. Visual Studio configurations are what controls the deployment options and destination. Those are per project. Yes, SSIS package configurations (dtsConfig files, for example) can be global.

2B) Good idea. Maybe we can just automatically spit out a .bat file to the bin directory or something if that deployment option is selected. I'll put that on the list to consider.

2C) Sticking that stuff in a .bat file basically as a template may be a good compromise. We'll consider it.
Coordinator
Jun 25, 2008 at 7:33 PM
I just checked in code changes to tackle 2A and 2B. I have not attempted 2C yet and don't anticipate doing that very soon. But we're still considering 2C.

We would love your feedback on the code changes to see if they meet your needs. The changes are described in the updated wiki for this feature: http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Deploy%20SSIS%20Packages
Jun 25, 2008 at 8:04 PM


furmangg wrote:
I just checked in code changes to tackle 2A and 2B. I have not attempted 2C yet and don't anticipate doing that very soon. But we're still considering 2C.

We would love your feedback on the code changes to see if they meet your needs. The changes are described in the updated wiki for this feature: http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Deploy%20SSIS%20Packages



This looks great - thank you!

I'll have to download the source code and take a look. I've always been a "wait for the packaged release" user of BIDS Helper in the past, but this looks like compelling functionality to me.