SSIS: Read customized configuration data from SQL table to multiple package variables

Microsoft SQL Server Integration Services provides multiple ways to configure the package. These package configuration types are as below:

• XML configuration file
• Environment variable
• Registry entry
• Parent package variable
• SQL server

If you select the SQL Server configuration type, a default "SSIS Configurations" table will be used to store the configurable data. But sometime you need to use customized configurable table, which SSIS does not support for automatic configuration. So below solution can be one of the way of implementation:

Lets consider our configurable table is as below:

And list of our SSIS package variables as belw:

Here variables "V_SCHEMA_NAME", "V_SCHEMA_RESULTSET" and "V_SCHEMA_ROW" are used internally for looping and data storage.

Below are the steps to create a SSIS package to read the confiuration values from SQL server table into our SSIS package variables.
Steps:

1. Add "Execute SQL Task" and set the properties as below:

Tab	Properties	Value
General	Name	Execute SQL Task - Get Schema List
	ResultSet	Full result set
	ConnectionType	ADO.NET
	Connection	SSISTest
	SQLSourceType	Direct input
	SQLStatement	SELECT DISTINCT [SCHEMA_NAME] FROM [dbo].[CFG_SCHEMA_CONN] WHERE [ISACTIVE] = 1
Result Set	Result Name	0
	VariableName	User::V_SCHEMA_RESULTSET

2. Add "Foreach Loop Container" and set the properties as below:

Tab	Properties	Value
General	Name	Foreach Loop Container - Loop through each Schema
Collection	Enumerator	Foreach ADO Enumerator
	ADO object source variable	User::V_SCHEMA_RESULTSET
	Enumeration mode	Rows in the first table
Variable Mappings	Variable	User::V_SCHEMA_NAME
	Index	0

3. Add "Execute SQL Task" inside the foreach loop container and set the properties as below:

Tab	Properties	Value
General	Name	Execute SQL Task - Get data for schema
	ResultSet	Full result set
	ConnectionType	ADO.NET
	Connection	SSISTest
	SQLSourceType	Direct input
	SQLStatement	SELECT [REPORT_CONN_STR], [PROD_CONN_STR] FROM [dbo].[CFG_SCHEMA_CONN] WHERE [SCHEMA_NAME] = @SCHEMA_NAME
Parameter Mappings	Variable Name	User::V_SCHEMA_NAME
	Direction	Input
	Data Type	String
	Parameter Name	@SCHEMA_NAME
	Parameter Size	-1
Result Set	Result Name	0
	Variable Name	User::V_SCHEMA_ROW

4.Add "Script Task" inside the foreach loop container and set the properties as below:

Tab	Properties	Value
General	Name	Script Task - Store data in package variable
Script	ScriptLanguage	Microsoft Visual C# 2008
	EntryPoint	Main
	ReadOnlyVariables	User::V_SCHEMA_NAME, User::V_SCHEMA_ROW
	ReadWriteVariables	User::V_SCHEMA1_PROD_CONN_STR, User::V_SCHEMA1_REPORT_CONN_STR, User::V_SCHEMA2_PROD_CONN_STR, User::V_SCHEMA2_REPORT_CONN_STR, User::V_SCHEMA3_PROD_CONN_STR, User::V_SCHEMA3_REPORT_CONN_STR, User::V_SCHEMA4_PROD_CONN_STR, User::V_SCHEMA4_REPORT_CONN_STR, User::V_SCHEMA5_PROD_CONN_STR, User::V_SCHEMA5_REPORT_CONN_STR
	Edit Script	public void Main() { try { DataSet ds = Dts.Variables["V_SCHEMA_ROW"].Value as DataSet; if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { DataRow dr = ds.Tables[0].Rows[0]; string schemaRptConnStr = Dts.Variables["V_SCHEMA_NAME"].Value.ToString() + "_REPORT"; string schemaProdConnStr = Dts.Variables["V_SCHEMA_NAME"].Value.ToString() + "_PROD"; foreach (Variable v in Dts.Variables) { if (v.Namespace.Equals("User") && v.Name.Contains(schemaRptConnStr)) { v.Value = dr[0].ToString(); } if (v.Namespace.Equals("User") && v.Name.Contains(schemaProdConnStr)) { v.Value = dr[1].ToString(); } } } Dts.TaskResult = (int)ScriptResults.Success; } catch { Dts.TaskResult = (int)ScriptResults.Failure; } }

The SSIS package will look like as below: