Initialize SSIS Variables from a Database Table
y2008m03d28
Tom VdP
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).
Thanks for posting this, it’s really nice!
Thanks,
Excellent post.
I am having issues making this work. Can you post the SSIS package so that I can see what the issue is?
Kevin, I cannot disclose the package in which I use this code. But if you give some more details about the “issues” you are having I will be glad to help.
I have changed the name of the db connection in the script and when I run the script it executes successfully but it does not change the variables to the ones stored within the database (SQL Server 2005). The db schema is below. I figure it is something small, but I have yet to pinpoint it. Let me know what additional information may be useful.
[dbo].[SSISCubeAutomationVariables](
[OLAPdbName] [varchar] (100) NOT NULL,
[Month] [varchar] (20) NULL,
[Year] [varchar] (10) NULL,
[ParameterName] [varchar] (100) NOT NULL,
[ParameterValue] [varchar] (4000) NULL,
[Description] [varchar] (4000) NULL )
ON [PRIMARY]
It is probably the check for the existence of the variable in the While sqlResult.Read()-loop that returns false. Variable names are case sensitive, so make sure the value of the ParameterName in the table matches the name of the SSIS parameter exactly.
If this does not help you will have to resort to adding debugging messageboxes.
Very nice. Just wondering what exactly you mean by “all the parameters are string types”. Are you talking about the parameters in database table only or both in the table as well as the variables in SSIS package? Could the package variables be of different datatypes?
If you want to assign a value from the database table to an SSIS variable of, say, type Int32 then you will have to cast de.Value in the code above to integer. Otherwise you get an error: Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Dear Friend,
Good post and blog.
But tell me why you need all this using script, you you have SSIS Package Configurations?
Or you can use only SQL Task to get the values to a database table and store in a SSIS variable.
Regards,
Pedro
Pedro,
The idea is that all logic is contained within a single SQL Server database. When deploying to “server xyz” then the parameter values stored in “server xyz” will be used. That is much cleaner than having to distribute a configuration file with the SSIS packages and adapt their contents per specific deployment.
Of course, there is one caveat: you have to make sure the packages connect to the correct database. This is done by “overruling” the data source connection strings in the SQL Server Agent: it is one of the job step properties.
There are probably a lot of scenario’s where using configuration files is the better approach. But for a project where packages are run via SQL Agent jobs, I prefer the approach described above.
I tried the samething in my script task. It neither returns any error nor returns desired results. While debugging I found the variables collection count is always getting displayed as 0 inspite of variables declared at the package level scope. Any help will be appreciated.
Thanks,
AK
Make sure that the variable names in the package and in the table match exactly including casing: if you have a “myVariable” in the table and a “MyVariable” in the package then they will not match.
Also, the Variables collection is empty because you do not supply any variables in the script component’s ReadOnlyVariables or ReadWriteVariables, which is good because the script is supposed to be generic. It looks for any variable defined in the package.
Don’t forget that the Mor% – sorry, the Cre& – sorry, the swell guys at Microsoft managed to localize the name of the default namespace. I’d bet this accounts for at least some of the other commenters’ problems.
And by the way, I prefer doing this in a data flow like:
Public Class ScriptMain
Inherits UserComponent
Dim oRegEx As Regex
Public Sub New()
oRegEx = New Regex(“[^a-zA-Z0-9_]+”)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim iKey As Int32
Dim sName As String
Dim vars As IDTSVariables90 = Nothing
If Not Row.ikey_IsNull AndAlso Not Row.gname_IsNull AndAlso Not Row.tname_IsNull Then
iKey = CType(Row.ikey, Int32)
sName = oRegEx.Replace(Row.gname, “_”).Replace(“__”, “_”) & “::” & oRegEx.Replace(Row.tname, “_”).Replace(“__”, “_”)
Try
Me.VariableDispenser.LockForWrite(sName)
Me.VariableDispenser.GetVariables(vars)
Catch ex As Exception
Me.ComponentMetaData.FireWarning(1001, “Parameter Reader”, sName & ” not found.”, Nothing, 0)
End Try
If Not vars Is Nothing Then
Try
vars(sName).Value = iKey
Me.ComponentMetaData.IncrementPipelinePerfCounter(103, 1)
Catch ex As Exception
End Try
vars.Unlock()
End If
End If
End Sub
End Class
- Note I thought performance impact of locking/unlocking the vars one by one is negligible, and that this pipeline comes with the fields ikey int, gname wstr and tname wstr, and that I only needed integer parameters in this case.
- Also note the error handling is not clean – am fighting the docs tring to find the specific exception type thrown for a missing name and losing.
- Also note the names in the database are not under my control, hence the sanitizing RegEx.