Multiple Configurations in SSDT

Case
In the SSIS Catalog we have the ability to configure for several environments by creating multiple environments (one for development, one for test), but how do we easily switch environments in SSDT while developing?

Multiple environments

Solution
In SQL Server Data Tools (SSDT) we have the Configuration Manager that can do something similar.


1) Start
Lets start with a basic package which loads a flat file into a database table. The two connection managers should be changed if we want to load data from the Test environment instead of data from the Development environment.

My basic package

2) Package Parameter
We have to create a Package Parameter for the Flat File Connection Manager. (This Connection Manager is for this package only.) Right click the 'Clients' Connection Manager and click Parameterize. Select the ConnectionString as the property. Create a new parameter and set the scope to Package.

Parameterize Connection Manager

3) Project Parameter
Do the same for the OLE DB Connection Manager that connects to SQL Server. Because this is a Project Connection Manager, the Scope is automatically set to Project

Parameterize Connection Manager

4) Add Configuration
Next step is to add a Configuration in addition to the existing Development Configuration. In the Standard toolbar you see a drop down called Solution Configurations. Open it and select Configuration Manager. A new window will open.
In the Active solution configuration drop down click on <New...>. Now you can enter a new name and optional copy settings from the existing Development Configuration.

Adding a Configuration

5) Add Parameters to Configuration - Package Parameter
Now we have two Configurations (Developement and Test) and we can give the parameters different values for each Configuration. First the Package Parameter: Go to the Parameter tab in the package and click on 'Add Parameters to Configuration'.
Next, click on the Add button in the new window to select a Parameter to configure. In the next new window you can select that variable.

Add Parameters to Configuration

Now you have added the Parameter to the Configurations, you can give it a different value for each Configuration.

Change Parameter value for Test Configuration

6) Add Parameters to Configuration - Project Parameter
Now do the same for the Project Parameter. In the Solution Explorer (default upper right corner) you can find the Project Parameters. This will open a new document. The rest is the same as the previous step.

Add Project Parameters to Configuration

Change the parameter value for the Test Configuration.


Change Project Parameter value for Test Configuration

7) Test
Now you can run the package with different Configurations within SSDT. In this example a small file for Development and a large file for Test.

Running with different Configurations

Note 1: These configurations won't be deployed to the SSISDB.
Note 2: You can also change other project properties per Configurations, like the deployment path or the 32/64bit property.

Configure other project properties