Implementing SQL server package configuration in SQL Server Integration Services

Scenario - There are several ways to implement configuration in SQL Server Integration Services. As per my understanding with SSIS packages each type of configuration has a special quality and it is useful in a specific scenario. Like SQL Server configuration, it is very useful when we need to store multiple parameters using the configuration. In this scenario SQL Server configuration will be more useful than XML or other configurations, because SQL server table configuration provides you the database table to hold the parameter values, you can easily update them using SQL update query, while in the other hand if you use XML configuration for the same, it may contain a bunch of XML code which may be bit difficult read and understand.

Usage of SQL Server configuration
SQL Server configuration is more useful whenever it is required to store the multiple parameters in the SSIS configuration.

Implementing the SQL Server configuration -

Let's create a simple and short implementation of SQL Server package configuration.It will store the variable value in a SQL server table and later we will check it at the package level.

Step 1 - Open SQL server Business Intelligence Development Studio and create a new Integration Services Project -

Step 2 - Let's create a Oledb connection using local SQL server database.

Click on OK button, Oledb connection will be created as per the below screen shot -

Step 3 - Create a variable at package level and let's name it ConfigVar.

Step 4 - Right click and select Package Configuration -

Step 5 - Select the checkbox "Enable package configuration" and click on Add button in the below wizard-

Step 6 - Select SQL Server from the Configuration type and pass the oledb connection which we have created at Step 2.

Step 7 - If SSIS Configuration table is not exist in the database then it is required to create a new SSIS Configuration table, you can create SSIS Configuration table by click on the New button. Also you can rename it. Once click on OK button SSIS Configuration table will be created and also selected by default in the above wizard.

Step 8 - Once you are done with the SSIS Configuration table, provide value to Configuration filter to group a set of entries. Let's name it TestFilter and click on Next button. 

Select the value of the variable ConfigVar which we have created at Step 3 and click on Next button.

Step 9 - Provide name of the configuration and click on Finish button and also close the configuration wizard.

Here SQL Server configuration has been completed for the variable ConfigVar.

Step 10 - . To verify the same just check the dbo.[SSIS Configurations] table in the database -

ConfiguredValue field will store the value of the variable, As of now it is empty and can be udpated using SQL update statement.

Step 11- Let's edit the ConfiguredValue and fetch it from the SSIS package in order to test the SQL Server configuration.

Step 12 - We will fetch the value of the variable ConfigVar though ScriptTask, and it should be provided the same value which has been updated in the SSIS Configuration table.

Testing of SQL Server configuration

Let's take a Script task and pass ConfigVar variable as ReadOnlyVariables then we will populate the value of this variable using MessageBox.

Step 13 - Click on Edit Script... button and write the following code, It will populate a message box with the updated value of the variable.

Dts.TaskResult = (int)ScriptResults.Success;

MessageBox.Show(Dts.Variables["User::ConfigVar"].Value.ToString());

and click on OK button in the Script task.

Step 14 - Now run the same SSIS package, it should populate a message box with the same variable value which is edited at Step 11.

As per the above screen shot we are getting the same value which was stored previously at Step 11. SQL Server configuration has been completed successfully.

I hope this article will help you to understand on how to implement SQL Server configuration in a
SSIS package.

Please feel free to share your comments/suggestions.