SQL Server Indirect Configuration – SSIS

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

1 thought on “SQL Server Indirect Configuration – SSIS

  • Your work and contribution to building Microsoft based BI gurus (for lack of a bettwr word), like me, is phenomenal. Thought i would take my time to appreciate the contribution you have made to my short BI career this far.

Leave a Reply

%d bloggers like this: