SSIS: Reading and Writing to Variables in Script Task

A lot of people still consistently ask me about how to read and write to variables in the SSIS script task. In this post I will demonstrate for you the two ways in which you can go about this, one from native SSIS functionality and the other from code. In SQL Server 2008 both will generally work out equally as well. In previous versions, you may want to stick with the coding piece as sometimes the ‘automatic’ integration with the variable dispenser was a little hooky.

So for our example, I am going to set up a simple SSIS project. First I create 2 variables: MyName & YourName. I have scoped these at the packages level as it has always been my instinct that unless there is a compelling reason to scope it otherwise.

Now that we have our variables set up, we are ready to begin making our project. Since this example is going to be pretty straightforward, we will just drop two script components onto the control flow design surface. The first will be the one using the native way of handling variables in the script component and the second will be using a little bit of custom code to do it programmatically. The setup is simply shown below.

Now opening up the script task you can click on the … button next to the readonlyvariables and readwritevariables areas. For this sample, I am setting up the MyName variable to be read only and the YourName variable to read-write.

When completed the screen will show our two variables in the boxes as shown below.

Now it is a matter of simply making a call, like below, to access your variables. For my example I am simply going to display the values in some message boxes. Reading from the MyName variable and overwriting the YourName variable. Pretty simple.

    Public Sub Main()

        Dim MyName As String = Dts.Variables("MyName").Value

        MsgBox(MyName)

        Dts.Variables("YourName").Value = "Tom"

        Dim YourName As String = Dts.Variables("YourName").Value

        MsgBox(YourName)

        Dts.TaskResult = ScriptResults.Success

    End Sub

The variables integration with the script tasks has gotten much better in SQL Server 2008 and I have yet to run into any problems like I have previously. Still, there may be some instances that you come across that you would rather programmatically access variables. For this the code below should do the trick. I have two functions: one for reading a variable and the other for writing to a variable. You will notice in both instances that you must lock the variable first before trying to access it. Think of it as SSIS’s version of row locking. You want to make sure that you are getting the most accurate version of the variable at that point in time. What good would it do you,since things can run in parallel, if another task is updating the variable at the same time you are trying to read it? 

    Public Sub Main()

        Dim MyName As String = ReadVariable("MyName")

        MsgBox(MyName)

        WriteVariable("YourName", "Tom")

        Dim YourName As String = ReadVariable("YourName")

        MsgBox(YourName)

        Dts.TaskResult = ScriptResults.Success

    End Sub

    Private Function ReadVariable(ByVal varName As String) As Object

        Dim rtnValue As Object

        'Create a variables collection to hold you object

        Dim var As Variables

        Try

            'Lock the variable first to make sure that you have exclusive access

            'Think of it like a database object lock

            Dts.VariableDispenser.LockOneForRead(varName, var)

            'Now populate your result

            rtnValue = var(varName).Value

        Catch ex As Exception

            Throw ex

        Finally

            'You must make sure that you unlock the variable before exiting routine

            var.Unlock()

        End Try

        Return rtnValue

    End Function

    Private Sub WriteVariable(ByVal varName As String, ByVal value As Object)

        'Create a variables collection to hold you object

        Dim var As Variables

        Try

            'Lock the variable first to make sure that you have exclusive access

            'Think of it like a database object lock

            Dts.VariableDispenser.LockOneForWrite(varName, var)

            'Now populate your result

            var(varName).Value = value

        Catch ex As Exception

            Throw ex

        Finally

            'You must make sure that you unlock the variable before exiting routine

            var.Unlock()

        End Try

    End Sub

Please note on the code above it is also possible to use something like the following for locking and getting the variable into your collection

  Dts.VariableDispenser.LockForRead(varName)

  Dts.VariableDispenser.GetVariables(var)

But it includes an extra step so it is not the most elegant solution.  Additionally, you may notice that in the Finally block. This is not required as Microsoft ‘promises’ in their documentation that variables are automatically unlocked when the execution of the script task stops. So you can trust that will happen or call it explicitly like me just so that you will sleep better at night.

Hopefully, this helps out some of my colleagues out there that may be struggling finding a good outline of this in the documentation.

Cheers,

AJ

Author profile:Arie Jones

is the Director of Emerging Technologies for Perpetual Technologies, Inc in Indianapolis, IN. where he regales his other team members with fanciful tales of database and development lore. AJ specializes in SQL Server and application development technologies …and especially likes all topics that include talk of Silverlight.