SSIS has a really versatile set of configuration options that make it (almost) easy to promote packages from dev to production. For anyone in the Denver CO area I’ll also be doing a presentation on this on Saturday at the SQL Saturday there. If you haven’t registered yet, please do so! It really is a great way to network with your peers on a local and national basis. To register, go here:
There are 5 different ways we can promote our code through our different environments. They are, in no particular order:
- registry key
- xml configuration file
- sql config table
- parent child connection
- environment variable
Over the next couple of weeks, I’ll outline each of these in detail. Today we’ll cover XML Configuration file
XML configuration files can be incredibly powerful and your worst enemy at the same time. We can use XML configuration files to configure multiple properties from file that exists on the server, or we can use it as a master config file to point the rest of our configuration options in our packages to a SQL table to resolve the rest of our connections. To access Package Configurations in BIDS 2008, go to the menu bar at the top of the page and select “package configurations” from the list.
Check the box in the package configurations pane that appears and click “Add”
A screen similar to the below will appear:
Select “XML Configuration file” from the dropdown and then select the Browse button that is active. Select a location and give your folder a name.
Check the box next to the property you wish to change.
Click next again, give it a name that makes sense so you can reference it. As a standard, I usually give it the same name as the connection (or whatever it is) that I’m configuring. Then it’s easy to reference if you want it again later.
In a future post, I’ll go over the additional ones. 🙂