All about Business Intelligence

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