One of the first of many pains-in-the-butt that SQL Server Integration Services developers will encounter is how from a Script Task in the Control of Flow to write to and read from package variables. I have blogged a bit about the topic of SSIS variables in script tasks [1] in the past, but in this post I'd like to share two custom functions that I copy and paste into all of my script tasks to simplify dealing with variables. If you use these functions you will not need the Script Task Edtior's ReadWriteVariables field to make your package variables available to the script.

First, the ReadVariable function:

Private Function ReadVariable(ByVal varName As String) As Object
    Dim result As Object

    Try
        Dim vars As Variables
        Dts.VariableDispenser.LockForRead(varName)
        Dts.VariableDispenser.GetVariables(vars)
        Try
            result = vars(varName).Value
        Catch ex As Exception
            Throw ex
        Finally
            vars.Unlock()
        End Try
    Catch ex As Exception
        Throw ex
    End Try

    Return result
End Function

And WriteVariable():

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
    Try
        Dim vars As Variables
        Dts.VariableDispenser.LockForWrite(varName)
        Dts.VariableDispenser.GetVariables(vars)
        Try
            vars(varName).Value = varValue
        Catch ex As Exception
            Throw ex
        Finally
            vars.Unlock()
        End Try
    Catch ex As Exception
        Throw ex
    End Try
End Sub

The functions are pretty straightforward once you've grokked the VariableDispenser and locking concepts, but if you'd prefer not to grok, these functions make it easy to remain blissfully ignorant. If you read the code closely you will see that this is not necessarily the optimal technique performance-wise because it only deals with one variable at a time. Notice that the call to VariableDispenser.GetVariables() only gets one variable. If your Script Task is reading and writing from many variables, then accessing them for reading and writing one at a time in this way might not be the most efficient technique--but my guess is you'd have a hard time measuring the impact of any theoretically better performing techniques, and the price you'd pay in the loss of code simplicity and understandability would outweigh any possible performance benefits.

(This goofiness, in my interpretation, is anyway all a side effect of the clumsy and awkward integration between SSIS, BIDS, and this VB.NET scripting engine--but that's a whole other rant. Even useful functions like these cannot save you if you are unfortunate enough to forget the exact spelling of one of your package variables while you're coding your script; you'll still have to go down and up the stack of modal dialogs to go look it up and come back to your code--OK, I must resist getting full into this rant...)

Getting back on track, here is an example of using the ReadVariable() function:

Public Sub Main()
    'Assume success
    Dts.TaskResult = Dts.Results.Success

    Dim jobId As Integer
    jobId = CType(ReadVariable("JobId"), Integer)
End Sub

Notice that the variable name is passed as a string. Notice also that the value that comes back is a System.Object. Notice how you have to use CType to cast the value to the type you really want. Notice also how this code makes a big assumption that CType is going to work just fine--that is, we're assuming that the JobId variable will indeed hold a valid Integer value. If there's any doubt about this, I advise additional code to test the value before attempting to cast it.

(If you're not familiar with the technique of setting Dts.TaskResult at the top of Sub Main, check out my blog post on why I use this convention [2].)

Here's a WriteVariable() example:

WriteVariable("SomeBooleanVariable", True)

Notice how the second argument of WriteVariable() is also a System.Object. This means you can pass an valid VB.NET value. However, you still need to be cognizant of what the "data type" of the variable is as it is defined in the package. That is, if it's a numeric variable, for example, don't try to write an alphanumeric string to it.

Hope this helps,
Dan