Using Visual Studio configurations in SQL Server Integration Services projects

By:   |   Comments (2)   |   Related: More > Integration Services Development


I have a SQL Server Integrations Services (SSIS) 2012 project. I have multiple packages that can read data from multiple servers. In order to test my packages thoroughly, I’d like to change the connections so that I can test the packages for each server. This can be done in the SSIS catalog by using parameters and environments (please refer to the tip SSIS Project Deployment Model in SQL Server 2012 (Part 2 of 2) on how to achieve this), but I’d like to do it inside the Visual Studio environment for debugging purposes, without me having to change all the connection strings manually. Can this be achieved?


By using Visual Studio configurations, you can mimic the behavior of the environment of the SSIS catalog to some extent. In this tip, we’ll walk through the set-up of two different configurations that will allow us to dynamically change the behavior of a package.

SSIS Package Set-up

The test package is straight forward: it contains a single data flow. In the data flow, there is an OLE DB source that will read data from a database using a simple T-SQL SELECT statement.

Package Layout

The following script will create the test table and insert the test data.


CREATE TABLE dbo.Department
 (DepartmentID INT IDENTITY(1,1)
 ,DepartmentName NVARCHAR(50));

INSERT INTO dbo.Department
VALUES ('HR'),('Sales'),('Finance'),('Engineering');

The connection manager OLE_TEST is a project connection manager. In order to make it dynamic, a project parameter is defined that will store the database name.

Project parameter

This parameter is used in an expression on the project connection manager in order to make it dynamic.

Project connection

The point of this tip is finding a way to change this project parameter value without manual intervention. This might seem superfluous for only one parameter value, but imagine a project with dozens of parameters. Manually changing all of them would be a tedious task.

Visual Studio Configurations

Visual Studio offers a solution for this issue in the form of configurations. In order to find those, you need to go to the project properties. Make sure Configuration Properties is selected in the left pane, then click on the Configuration Manager… button.

Configuration Manager

This will open Configuration Manager, where you can edit existing configurations and create new ones. In a newly created SSIS project, there is only one solution configuration present, called Development.

Solution Configurations

Let’s rename this configuration to Test. Click Edit… and then rename it to Test.

Edit Solution Configurations

Since a solution can contain multiple projects in Visual Studio, a solution configuration can contain multiple project configurations. Since there is only one project in our solution, you can see only one line in the Project contexts. There you have a default project configuration, also called Development.

Project Configurations

Let’s rename that project configuration to Test as well. In order to test our configurations, we’ll try to change the database name to AdventureWorksDW2012. Therefore we need to create a new solution configuration. Let’s call it AWDW and copy the settings from the Test configuration.

New Solution Configuration

Now we need to link the configurations to the project parameter. First of all, make sure the Test configuration is selected as the active configuration. You can do this with the dropdown menu next to the debug button.


Next, open up the project parameters window. There you have a button for adding parameters to existing configurations.

Add parameter to config

When clicking the button, a new dialog will open. By clicking on Add, you can select a parameter that will be added to the configurations.

Manage parameter values

Now you can specify parameter values for the different configurations.

Specify values

Once this is done, you can now dynamically change the parameters by changing the configuration in the dropdown window. When using the Test configuration, everything works as expected.

Running the package with the test configuration

However, when changing the configuration to AWDW, the package immediately errors out at validation because the table dbo.Department doesn’t exist in the AdventureWorksDW2012 database.

Running the package with the AWDW configuration


By using configurations inside Visual Studio, you can dynamically change the behavior of SSIS packages. This is done by storing different values for parameters inside the configurations, which is similar to environments and environment variables inside the SSIS catalog on the server level.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, February 20, 2020 - 3:10:55 PM - Koen Verbeeck Back To Top (84681)

Hi Prathap,

what is the error?

Wednesday, February 19, 2020 - 11:20:51 PM - prathap Back To Top (84656)

Integration Services Catalog, type the database instance name in the Server name text box or click Browse and select the database instance that contains the catalog

while selecting this i am getting an error

get free sql tips
agree to terms