Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Visual Studio configurations in SQL Server Integration Services projects


By:   |   Read Comments   |   Related Tips: More > Integration Services Development

Attend these FREE MSSQLTips webcasts >> click to register


Problem

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?

Solution

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.

USE TestDB;
GO

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.

Dropdown

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

Conclusion

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools