Setting Variables in the Script Task in SSIS

One of the key reasons that you use the script task is to change the value of a variable at runtime. There is a lot of real-world scenarios that you would use this for. If you’re reading this you have already probably thought of a few. To set a variable in the script task, there are two main 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 ReadOnlyVariables and ReadWriteVariables options. 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. Hope this helps!