Multiple SSIS environments on the same server

Got Dev and Test (and even Prod) environments on the same box? Want to avoid hacking the package xml when promoting SSIS solutions? There are a couple of configuration options:

1. XML configuration file

The key here is to use a relative path. The wizard guides you to click the Browse button and specify an absolute reference e.g. c:\ssisdev\config.dtsconfig. Instead, just type in config.dtsconfig. SSIS will look for the config file in the package location.

Depending on how you structure your solution, you don't necessarily want your config files in the same location as the packages that use them. I suggest you give up on this now and accept all config files will have to be in the same location as the packages that use them.

This is because longer relative paths such as ..\config.dtsconfig don't work (this particular example saves the config file to C:\Program Files\Microsoft Visual Studio 8\Common7 !!!)

2. SQL Server

This is a two phase approach. Firstly, set up a SQL Server configuration table. As part of this process you will reference or create a database connection. Then (and this is the cunning bit) create an XML config file with a relative path (as per option #1) that contains the connection string for the connection. You can then specify different configuration table locations in the xml config files.

This option also uses xml config files and is hampered by the same difficulties as option #1. You will need a separate config file for every package location, and you will need to maintain these config files individually. You could design to ensure your config files are all identical and write a script to copy them as part of the build; whether this is worth the effort depends on your solution.