Initialize SSIS Variables from a Database Table

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: ssispar.GIF

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

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

22 Comments »

Comment by Dave Neeley
2008-05-15 20:09:43

Thanks for posting this, it’s really nice!

 
Comment by Prasannakumaran Sarasijanayanan
2008-06-05 10:56:59

Thanks,
Excellent post.

 
Comment by Kevin
2008-08-05 19:30:34

I am having issues making this work. Can you post the SSIS package so that I can see what the issue is?

Comment by Tom VdP
2008-08-05 21:26:55

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.

 
 
Comment by Kevin
2008-08-06 13:34:00

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]

Comment by Tom VdP
2008-08-06 14:01:30

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.

 
 
Comment by red
2008-08-13 20:01:22

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?

Comment by Tom VdP
2008-08-15 16:42:14

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.

 
 
Comment by PedroCGD
2009-01-06 01:11:02

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

Comment by Tom VdP
2009-02-12 17:10:55

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.

 
 
Comment by AK
2009-09-16 15:11:47

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

Comment by Tom VdP
2009-09-17 09:41:12

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.

 
 
Comment by Robert
2010-03-23 11:58:46

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.

 
Comment by Bruce Dunwiddie
2010-11-18 00:53:07

This will attempt to handle the casting for you from the value retrieved from the database to the correct data type of the variable in the package. So if the value in the database is a numeric stored in a varchar field and the variable data type in the package is an Int32, this will do the parsing and set the variable value successfully.

vars(de.Key).Value = Convert.ChangeType(de.Value, vars(de.Key).DataType)

 
Comment by Harry Simpson
2011-03-01 18:58:28

Works great!! Thanks!!

 
Comment by Sateesh
2011-09-11 03:54:39

Hi,
Great Post!!!
i don’t know why i am getting dts is not declared variable in script task.

very glad to guys if you could suggest me something to towards this issue.

Thanks
Sateesh

Comment by zed
2012-02-01 21:58:14

I also getting dts not declared variable in script task. Anyone solved it?

 
 
Comment by Marc
2011-09-27 18:16:29

I\’m a rookie in SSIS.

Is it also possible with this script to use mult-value-parameters?

I need to add a condition in a conditional split like \’country in @countries\’.
Should the parametervalue one string like \’UK, Spain, Belgium\’
or should I add a record for each country where the parametername is the same
in every record?

Thanks,

Marc

 
Comment by Omar
2011-09-27 21:35:29

Great code, simpler than the others I came across, including MSN’s !
The only problem I had was that the connection is case sensitive!
Omar.

 
Comment by zed
2012-02-01 22:00:27

Im also facing: <>
Anyone solved this issue? Thanks

 
Comment by ken
2015-01-13 15:39:10

I am new to ssis and your article leaves so many gaps on how to create each item used that this article is useless to me.

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post