As part of my standard approach (and the one used by Mariner), I use configurations in SSIS quite heavily. My preference is to use the SQL Server configuration type, since it keeps all the configurations in one place. XML configurations tend to result in a lot of extra files to manage. With SQL Server based configurations, though, you need to provide the initial connection string to the SQL Server. We typically use a system environment variable to control that initial connection to the configuration table (either directly, using a specific configuration to control the connection as Rafael has show in the past, or using the indirect approach, as highlighted by Jamie). We use a separate connection manager for the SQL Server configuration connection, usually named Configuration (I like self-explanatory names).
One of our current clients has their development and QA databases as separate SQL Server instances on the same server. There a number of reasons why I don’t care for that approach, but that’s another post. We also needed to use this same server as the host for both the development and QA versions of the SSIS packages. This posed a problem, as the both versions of the package would pick up the same environment variable, so we would only be able to run for one environment at a time.
My initial thought was to use the /CONN switch of DTEXEC utility to override the Configuration connection. Unfortunately, this didn’t work. As best as I can tell, based on some information here and my own testing, the order of events at package load time is:
- Configurations are applied, based on what is set in the package at design time.
- Any /ConfigFile, /Connection, and /Set values are applied.
- Package validation is run.
- The package is executed.
Basically, what this boils down to is that there is no way to override a SQL Server configuration from the command line. Even setting the Configuration connection manager from an XML configuration on the command line doesn’t work, because by the time it is applied, the design time configuration is has already been set. Setting the connection string doesn’t force the configurations to be reapplied.
There are a few workarounds for this. You could apply an XML configuration at runtime to override all the configured values in the package, but that defeats the purpose of using SQL Server configurations. We ended up with a different approach, that still lets us use the SQL Server configurations. By setting the environment variable up as a user variable, we can override it based on the user account that the packages are run under. This has a few caveats as well. For one, you need a separate account for each unique environment (not unnecessarily a bad thing). Another is that you can’t use the Integration Services job step type to run the package. Evidently, it doesn’t create a full user session, which is needed to access user environment variables. You can work around it by using the CmdExec job step type.
If you find yourself needing to support multiple environments on the same server, using configurations can be difficult. Hopefully, this information will be helpful for determining the best approach.