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

  • Phishing Tops Concerns in Microsoft Study of Remote Work

    Potential phishing attacks were a top concern of most IT security professionals when organizations switched to remote-work conditions early last year.

  • How To Configure Windows 10 for Intel Optane Memory

    Intel's Optane memory technology can significantly improve the performance of your Windows 10 system -- provided you enable it correctly. A single mistake can render the system unbootable. Here's how to do it the right way.

  • Microsoft and SAP Enhance Partnership with Teams Integration

    Microsoft and SAP this week described continuing partnership efforts on Microsoft Azure, while also planning a Microsoft Teams integration with SAP's enterprise resource planning product and other solutions.

  • Blue Squares Graphic

    Microsoft Previews Azure IoT Edge for Linux on Windows

    Microsoft announced a preview of Azure IoT Edge for Linux on Windows, which lets organizations tap Linux virtual machine processes that also work with Windows- and Azure-based processes and services.

comments powered by Disqus