SSIS Package Configurations using SQL Server table...an alternative to the Indirect method

Recently, I was involved in a discussion about how to get the SQL Server based configurations work using the indirect settings; since I have not had the chance of getting into it; I thought it would be helpful to share a technique that yields similar results.

This post describes an approach for implementing SQL Server based package configuration that uses an environment variable to facilitate the deployment of the packages onto different servers without having to modify the packages. I have used this technique for a while, and I think, it may be used as an alternative to the indirect method described in BOL.

Let’s supposed we have a package that has a connection manager called ‘Source’, which has the connection information of the source DB where the package is trying to pull data from.:

Since during the development we are pointing to a development DB instance, we need the ability to change the connection information to point to the QA or Production source databases without the hassle of having to edit the package each time. It is here where package configurations come to the rescue. We have decided that the configuration values will be stored in SQL Server table; hence we need to create an additional connection manager that points to the location of our configuration table in the development environment. The name of this new connection manager will be ‘Configuration’.

The next step is to bring the configuration wizard and create an entry to set the connection string of ‘Source’ connection manager; it should be something like:

As you can see, this package configuration uses 'Configuration' connection manager to get acces to the Configuration table. On this example the name of the table is SSISConfiguration.

So, far we have used the direct method to get access to the configuration table; which is not a very portable solution as we have to manually change the connection information of ‘Configuration’ connection manager. So, here comes the trick: go back to the configuration wizard and create an entry to set the connection string of ‘Configuration’ out of an environment variable. In this example the environment variable is called SSIS_CONFIG; and its value is a connection string that point to the DB where the configuration table resides. Make sure you place this configuration above the other entries that use ‘Configuration’ (in this case, above of SourceConnectionEntry). Here is how my example looks like:

By doing this we have turned the package into a very portable one. Going forward, all we have to do, when deploying the packages into a different server, is to create a variable called SSIS_CONFIG in that server; set the proper connection string as its value, and make sure the rows in the configuration table have the right values.

Just in case, the value for the SSIS_Config is just a connection string that can be consumed by the connection manager; something like:

Provider=SQLNCLI.1;Data Source=MARINERLAPTOP14;Integrated Security=SSPI;Initial Catalog=ETLRafLabDev

I have successfully used this approach using an XML file instead of an environment variable; which comes handy when the use of the second ones are not an option. Please feel free to post your comments.

I hope you find this example helpful.

Update: Since I receive a lot of request asking for a copy of the package, I have make it available in my SkyDrive:


you would have to modify the connection strings to point to DBs that exists in your environment.