In many SSIS packages variables control the workflow. Here is a quick and generic way to load the initial values from a database table instead of having to hard-code them into the package or use configuration files.
The first thing you will need is a table that holds the parameter names and values. Here is an example of a recent project I worked on:
Note that I added an extra column Comments that contains information about each parameter. In the end I want the local administrator or power user to be able to change values in here to steer the process according to the business needs. The comments column is there to help that user to understand the meaning of the parameter. Of course a GUI to edit these values would even be nicer but that is out of scope for now.
One of the first tasks in the SSIS package is a script – SCR_ReadMainVariables in the picture below – that will initialize any variables defined in the package that have a matching name in the database table. Only matching variable names are affected, and not every parameter defined in the table must be defined in the package.
The script does not make use of the ReadOnlyVariables or ReadWriteVariables properties of the Script Task Editor, but instead accesses the variables directly via the VariableDispenser object.
Here is the code:
Imports System Imports System.Collections Imports System.Data Imports System.Data.OleDb Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Try Dim ht As New Hashtable(20) Dim c As New OleDbConnection( _ Dts.Connections.Item("Datawarehouse").ConnectionString) Dim sqlCommand As New OleDbCommand( _ "select ParameterName, ParameterValue from Z_Parameters", c) c.Open() Dim sqlResult As OleDbDataReader sqlResult = sqlCommand.ExecuteReader() While sqlResult.Read() If Dts.VariableDispenser.Contains( _ "User::" + sqlResult.GetString(0)) Then ht.Add(sqlResult.GetString(0), sqlResult.GetString(1)) Dts.VariableDispenser.LockForWrite("User::" + sqlResult.GetString(0)) End If End While sqlResult.Close() Dim vars As Variables Dts.VariableDispenser.GetVariables(vars) Dim de As DictionaryEntry For Each de In ht vars(de.Key).Value = de.Value Next vars.Unlock() Dts.TaskResult = Dts.Results.Success Catch ex As Exception Dim m As String = ex.Message While Not ex.InnerException Is Nothing ex = ex.InnerException m += vbCrLf + ex.Message End While Dts.Log(m, 0, Nothing) Dts.TaskResult = Dts.Results.Failure End Try End Sub End Class
There is one thing hard-coded: the name of the connection manager. So if you are going to use this code remember to rename “Datawarehouse” to whatever connection manager you want it to use.
Another thing: all the parameters are string types. Adding support for other types fits in the logic of the solution presented here, but you will need to add some things both to the table (a type indicator and possible more columns if the type does not fit in a varchar) and to the script component (casting and getting values from the correct columns).