posted on Friday, December 30, 2005 12:13 AM by bknight

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!

-- Brian Knight 

Comments

# re: Setting Variables in the Script Task in SSIS

Wednesday, March 22, 2006 8:37 AM by Bing

In the method 2, "you must ReadOnlyVariables and ReadWriteVariables options," I do not follow. Are you saying, AlertAdmin has to be added to both ReadOnlyVariables and ReadWriteVariables or something else? Thanks!!

# re: Setting Variables in the Script Task in SSIS

Friday, April 21, 2006 9:54 AM by dhibst
I have tried both methods (#2 first) and have not had success. I can read the variable but haven't been able to update the variable with a new value. I have a string variable with original text, set it to a new text value, show it using MsgBox, but the variable is not updated when the task finishes. Thanks for your help.

# re: Setting Variables in the Script Task in SSIS

Thursday, May 03, 2007 6:01 PM by Setting Variables in the Script Task in SSIS
Thanks bknight. This helped me correct my error and reference SSIS variables from my scripting task.

I believe bknight meant by 'you must ReadOnlyVariables and ReadWriteVariables options' is to add the name of the variable to the ReadOnlyVariables or ReadWriteVariables properties of the script task. This worked for me.

# re: Setting Variables in the Script Task in SSIS

Wednesday, June 27, 2007 7:36 AM by Hanslindgren
dhibst: Are you absolutely sure that you haven't duplicated your variable for the Scope of your Script Task container? If you have one declared at the package level and one at the Container level you will get this effect. Select your Script Task Container and look at the variables to see if you have two identical variables with different scopes. You have to select your Script Task Container otherwise you will not see this duplicated variable.

# re: Setting Variables in the Script Task in SSIS

Monday, November 26, 2007 9:26 PM by Madison
Very helpful, thank you! I understand this is required to pass in the variables, but if the variables are global to the package why must they be passed in at all? Seems like the script task should have access by default.

# re: Setting Variables in the Script Task in SSIS

Tuesday, February 19, 2008 2:45 PM by Raj Gujr
This Helped me to I just Added the Name of the Vairables in the ReqdOnly Variables and It worked.