Working with SSIS Variables in Script Tasks
You'll wonder no more how to manipulate SSIS Package Variables via Script Tasks, once you're done reading this little tidbit.
- By Eric Johnson
- 05/03/2010
While not a difficult task, the question of how to manipulate SSIS package variables in script tasks does come up quite often. Let's just dive right into an example to see how all the pieces interact.
In a sample package, I created two variables, ReadStringVar with a value of "Hello World" and WriteStringVar as an empty string (see Fig. 1).
 |
| Figure 1. Creating ReadStringVar and WriteStringVar. (Click image to view larger version.) |
Now that we have variables in SSIS, you have to set up the script task to be able to use these variables. This works the same in 2005 and 2008, except in 2005 you have to type in the variable name manually and in 2008 you have a pick list. In either case, open your Script Task properties and you should see two fields, ReadOnlyVariables and ReadWriteVariables. As you can see in Fig. 2, I set up my script task to read the ReadStringVariable and to read and write the WriteStringVariable.
 |
| Figure 2. Setting up script tasks to read ReadStringVar and to read and write WriteStringVar. (Click image to view larger version.) |
You can now access these variables in your script. This is done using DTS.Variables as shown here:
Public Sub Main()
MsgBox(Dts.Variables.Item("ReadStringVar").Value)
Dts.Variables.Item("WriteStringVar").Value = "Goodbye"
Dts.TaskResult = ScriptResults.Success
End Sub
The first part of this script will pop up a message box with the value of ReadStringVar and then write a new value to the WriteStringVar. Now when I execute my script task, it looks like Fig. 3.
 |
| Figure 3. What it looks like, when all is said and done. (Click image to view larger version.) |
There you have it, now you can write script tasks that read and write to SSIS variables.
One quick note: Although the dialog is slightly different, the method for accessing variables in Script Transformations is exactly the same. Happy coding.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.