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

  • Cloud Services Starting To Overtake On-Prem Database Management Systems

    Database management system (DBMS) growth is happening more on the cloud services side than on the traditional "on-premises" side, according to a report by Gartner Inc.

  • How To Replace an Aging Domain Controller

    If the hardware behind your domain controllers has become outdated, here's a step-by-step guide to performing a hardware refresh.

  • Azure Backup for SQL Server 2008 Available at Preview Stage

    Microsoft added the option of using the Azure Backup service to provide recovery support for SQL Server 2008 and SQL Server 2008 R2 when those workloads are hosted on Azure virtual machines.

  • Microsoft Suggests Disabling Old Protocols with Exchange Server 2019

    Exchange Server 2019 with Cumulative Update 2 (CU2) can help organizations rid themselves of old authentication protocols, which constitute a potential security risk.

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.