SQL Server Indirect Configuration – SSIS

SSIS Configuration is a handy option in SSIS deployment. there are lots of scenarios which can be implement with SSIS Configuration.
in this post I don’t want to explain what configuration is , you can read more about it here:
http://msdn.microsoft.com/en-us/library/ms141682.aspx

There are two types of configuration: Direct and Indirect.

in Direct configuration, the configuration path directly will store in Package.

But in Indirect configuration, the configuration path will store in an Environment variable value.

Indirect configuration will make the configuration path dynamic, and the admin can change the configuration path in the environment variable value.

In this post I’ll show you an example of how to set up Indirect SQL Server Configuration.

First of all, Create a simple package with single Script Task,

And inside script task write a message box to show a dummy message like this:

 Now run the package and check the result, an "Enabled!" message will show up;

Now we are going to set up a SQL Server Configuration;

click on empty area on control flow, then go to
Menu->SSIS->Package Configurations

Package Configurations Organizer window will show up, check the Enable Package Configurations, and then click the Add button,
Choose Configuration type as "SQL Server"
in the Specify configuration settings directly section;
in the Connection drop down, create a connection to the database which you want to create sql server configuration table there.
then click on Configuration Table drop down, click on the New button besides it, a "create table" window will appear,

SSIS will create a simple configuration table with default table name and field names, you can change it if you need, for this sample we go on with defaults.
then set a filter in Configuration Filter drop down , you can write any string there, this filter will categorize records in the configuration table, suppose that you have many configuration in many packages which all target this table as configuration table, and the configuration filter is good way to filter them up.
the overall of package configuration wizard till now is like this image:

click on Next,
you will redirect to Select Properties to Export step;
in the objects pane, you can select any objects or properties or variables which you want to add into configuration. Note that with SQL Server Configuration you can select multiple objects in single configuration.
in Objects pane, select the script task, and under properties, check the Disable property.

Click on the Next button, and choose a name for the configuration and finish the wizard.

You made a SQL Server Configuration successfully till now, but this configuration is direct.
in this step we are going to make it indirect;
Go to Control panel of windows, and go to Advanced System Settings tab, and click on Environment Variables button

in the User Variables sections, click on New button,
create new variable with name SQLConf, and value as :
"LocalHost.Test";"[dbo].[SSIS Configurations]";"TestSQL";

Note that the value made up 3 sections:
;;
you should enter appropriate values there. then click OK, and close the environment variables window.

Now go back to BIDS, first of all save all and close the BIDS and open it again, there are times which BIDS will not detect changes in environment variable when it is still running, after opening the package for second time,
go to SSIS->Package Configurations again and you will see SQL Server configuration from previous step here, click on it and select Edit,
in the Select Configuration Type step, select the "Configuation location is stored in an environment variable"
and in the drop down list find the SQLConf variable there.

then Click on Next, and finish the wizard, now you will see in the Package Configuration Organizer which the configuration type changed to "Indirect SQL Sever"

Now you created an Indirect SQL Server Configuration successfully, and you can test the configuration as below:
Go to SSMS, and select the SSIS Configuration table in selected database, and find the record of configuration, change the ConfiguredValue from False to True.

Now, run the package, you will see that the Message box won’t show up, and when you stop the package the Script task made Disabled.

That’s all.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.