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

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 www.consortioservices.com.

Featured

  • Microsoft Buys Orions Systems To Enhance Vision AI Capabilities in Dynamics 365

    Microsoft announced on Tuesday that it has acquired Orions Systems with the aim of enhancing Dynamics 365 capabilities, as well as the Microsoft Power Platform.

  • Microsoft Hires Movial To Build Android OS for Microsoft Devices

    Microsoft has hired the Romanian operations of software engineering and design services company Movial to develop an Android-based operating system solution for the Microsoft Devices business segment.

  • Microsoft Ending Workflows for SharePoint 2010 Online Next Month

    Microsoft on Monday gave notice that it will be ending support this year for the "workflows" component of SharePoint 2010 Online, as well as deprecating that component for SharePoint 2013 Online.

  • Why Windows Phone Is Dead, But Not Completely Gone

    Don't call it a comeback (because that's not likely). But as Brien explains, there are three ways that today's smartphone market leaves the door open for Microsoft to bring Windows back to smartphones.

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.