Quantix - SSIS script - Set variable at run time with a script task

For all the SSIS Developers out there this has been niggling me for sometime, so I thought I'd share this information with you,

To set a variable in the script task, here are two methods you can use.


Method 1

This method involves using the LockOneForWrite method in the VariableDispenser class. The advantage to this method is it allows for you to read and write to the variables at runtime without having to use the ReadOnlyVariables and ReadWriteVariables options in the Script task. The price of that though is that you have to write quite a bit more code. In the following example, I’m going to open the AlertAdmin variable for writing and then set it to the boolean value of True.

Public Sub Main()
Dim vars As Variables
Dts.VariableDispenser.LockOneForWrite("AlertAdmin", vars)
vars("AlertAdmin").Value = True
Dts.TaskResult = Dts.Results.Success
End Sub



Method 2

The second method is typically what I would recommend using just for simplicity. In this method, you would open the Script task and before clicking on Design Script, you must add the name of the variables to the ReadOnlyVariables or ReadWriteVariables properties of the script task. This is done by simply typing the name of the variable with no parentisis or namespace (e.g. @[User::strVariable] is written in this specific text box as strVariable). If you have more than one variable you wish to be available in the Script task, you can seperate them with commas. If you do not set this, you will not see an error at design time but at run time, you’ll receive the error shown below:
The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.


at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_8693feb81f2d4b7b8a26ae87a8a5f960.ScriptMain.Main() in dts://Scripts/ScriptTask_8693feb81f2d4b7b8a26ae87a8a5f960/ScriptMain:line 19

With the variables now being passed in, you can perform the same type of action as I showed in the earlier script in a single line (the second line shown below). The locking is done by the Script task UI.


Public Sub Main()
Dts.Variables("AlertAdmin").Value = True
Dts.TaskResult = Dts.Results.Success
End Sub

The ReadOnlyVariables and ReadWriteVariables options are available to you in the Expressions tab too if the variable name that you wish to pass in is unknown or dynamic. There are other ways to set the variables of course. Most tasks have hooks into the variables like the Execute SQL task.


Enjoy -

Robert....
Quantix