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.

 

image

 

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.

image

 

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.

image

image

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

image

 

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.

Leave a Reply

Your email address will not be published. Required fields are marked *