DTAP package configurations - part 2

Case
I have a DTAP (Development, Test, Acceptance, Production). How do I use SSIS configurations over these multiple environments?

Solutions
The most commonly used solutions for configurating multiple environments are:
a) XML Configuration File
b) SQL Server Configuration (in combination with environment variable)
c) SSIS 2012 (in combination with the new project deployment)

In this post I will eleborate SQL Server Configuration solution. For this scenario I have a simple package with one Data Flow Task that has one CSV source and one SQL destination. Let's start by adding configurations for the two Connection Managers.

Simple Scenario

1) Add configurations
Go to the SSIS menu and select Package Configurations... and then Enable package configurations. After that hit the Add button.

Enable package configurations

2) Configuration Type
Select SQL Server in the Configuration type selectbox. Create a new Connection Managers that links to the database with your configuration table (1). Create a new Configuration table or select an existing one (2). Enter a name that will describe the configurations (3). After that click Next.

Configuration Type

3) Select Properties to Export
In this step you select all the properties you want to configure in the config table. In our case we select the ConnectionString property of both Connection Managers (but not the one from the new connection manager that you created in the previous step!). After that click next.

Select the two ConnectionString properties

4) Configuration Name
Give your configuration a suitable name that will explain its purpose if you have multiple configurations.

Configuration name

5) Add Environment variable
We now have a third Connection Manager, but its connectionstring will be different on all servers in your dtap environment. So we need to configure that new Connection Manager as well. We will store its connectionstring in a Windows Environment Variable.
This step will vary for each Operating System. The screenshots are from my Windows 7 laptop. Go to the properties of "My Computer", then to Advanced system settings, then to the Advanced tab and then hit he Environment Variables button. Now add a new System variable with the name SsisConfigurationDatabase and the value must me the connectionstring of that new Connection Manager. Repeat this step for each ssis server in your dtap environment.

New System Variable

6) Add second configuration
Before we add a second configuration, you probably need to restart Visual Studio because it doesn't know your new Windows Environment variable. Add a new configuration and select Environment variable as Configuration type. Then select your new variable named SsisConfigurationDatabase. And click Next.

Configuration type

7) Select Target Property
Now select the connectionstring of the new connection manager and click Next.

Select the ConnectionString

8) Configuration name and move up
Give your configuration a suitable name and move it upwards so that it will be the first configuration. After that close the window.

Move it upwards!

9) Copy configuration database
Now copy the new configuration database table to all database servers in your dtap enviroment and make sure its values are adjusted to the server. Also make sure the environment variable is available on each server. Now you can deploy your package to each server and it will use its own configuration.