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.

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

Creating ReadStringVar, WriteStringVar
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.

setting up script tasks
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()

  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.

Scriptastic script tasks
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


  • Basic Authentication Extended to 2H 2021 for Exchange Online Users

    Microsoft is now planning to disable Basic Authentication use with its Exchange Online service sometime in the "second half of 2021," according to a Friday announcement.

  • Microsoft Offers Endpoint Configuration Manager Advice for Keeping Remote Clients Patched

    Microsoft this week offered advice for organizations using Microsoft Endpoint Configuration Manager with remote Windows systems that need to get patched, and it also announced Update 2002.

  • Azure Edge Zones Hit Preview

    Azure Edge Zones, a new edge computing technology from Microsoft designed to enable new scenarios for developers and partners, emerged as a preview release this week.

  • Microsoft Shifts 2020 Events To Be Online Only

    Microsoft is shifting its big events this year to be online only, including Ignite 2020.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.