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:
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
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 :
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.
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.