By: Koen Verbeeck | Comments (2) | Related: More > Integration Services Development
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.
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.
This parameter is used in an expression on the project connection manager in order to make it dynamic.
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.
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.
Let’s rename this configuration to Test. Click Edit… and then rename it to Test.
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.
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.
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.
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.
Now you can specify parameter values for the different configurations.
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.
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.
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
- Try it out yourself! You can download the entire SSIS project together with the configurations here. The project was created using Visual Studio 2010. This means it is not backwards compatible with earlier versions. Later editions of Visual Studio will upgrade the solution. The package also uses a database called TestDB and the AdventureWorksDW2012 sample database from Microsoft.
- For more information about the SSIS project deployment model, check out the following tips:
About the author
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 MSSQLTips.com. 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
