How to read write variable inside DataFlow ScriptComponent

In this article we will walk through how to read and write variables inside DataFlow.

Download Source Code (Sample SSIS Package)

You will need Task Factory Terminator Destination to test above sample package Download Free TaskFactory Components Here

You will also need Northwind Database to test above package.  Download Northwind Database Script Here

By default SSIS Only allows reading/writing variable in OnPostExecute Method (Not Visible By default - you have to code manully inside script component). This was by design to variable avoid locking overhead. But what if you have real need to update package variable value before / after and during processing?

Method-1 : Read/Write Variables **After all records are processed**

If you have need to read/write variable after all records are consume then perform the following steps

1. Edit properties of Script Component. Enter variables (seperated by comma) in ReadWriteVariables or ReadOnlyVariables 

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain1
    Inherits UserComponent

    Dim USAOrdersCount As Integer
    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        USAOrdersCount = 0
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        If Row.ShipCountry = "USA" Then
            USAOrdersCount = USAOrdersCount + 1
        End If
    End Sub

    Public Overrides Sub PostExecute()
        Me.Variables.varUSA = USAOrdersCount

        MyBase.PostExecute()
    End Sub
End Class

Method-2 : Read/Write Variables **Anywhere inside Script Component** without setting up ReadOnlyVariables or ReadWriteVariables properties

In previous section we saw how to access variable after processing is done but what if you want to read value before proecssing or during processing (inside OnPreExecute , ProcessInputRow)? If you try to access variables inside OnPreExecute using previous approach you may get the following error.

"The collection of variables locked for read and write access is not available outside of PostExecute."

So how to fix this error? How to access variables anywhere I want inside Script Component? Well we need to use some hidden SSIS APIs to Lock and Unlock Variables maually rather than setting up ReadOnlyVariables, ReadWriteVariables approach. Here is the code which will allow you to Read or write variables in script component without above error.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Dim myCounter As Integer
    Dim USAOrdersCount As Integer

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()

        '//Example ... How to read Variable before row processing 
        '//(Occurs once per dataflow execution when This script component is called in the pipeline)

        '//Initialize value here. Read from Package variable and assign to a variable 
        '//which will be alive for DataFlow Execution scope
        myCounter = Convert.ToInt32(ReadVariable("DFT::var1"))
        USAOrdersCount = 0
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        myCounter = myCounter + 1 '//Count rows

        '//Example ... How to Modify Variable during Row Processing

        '//This approach not recommended unless you  have any reason. 
        '//Use PostExecute to update any package variable that way you dont 
        '//introudce Variable Locking overhead. Inside  ProcessInputRow only use local variables if possible.
        If Row.ShipCountry = "USA" Then
            USAOrdersCount = USAOrdersCount + 1
            WriteVariable("User::varUSA", USAOrdersCount)
        End If
    End Sub

    Public Overrides Sub PostExecute()
        '//Example ... How to write Variable after all row processed 
        '//(Occurs once per dataflow execution when This script component is stopped consuming any buffer)

        '//Me.Variables.var1 = myCounter : If you want to do this way then 
        '//You must set ReadWriteVariables Property (Comma seperated variable list) 

        '//Or
        WriteVariable("DFT::var1", myCounter) '//This approach doesnt require setting ReadWriteVariables

        MyBase.PostExecute()
    End Sub

    Function ReadVariable(ByVal varName As String) As Object
        Dim vars As IDTSVariables90 '// or IDTSVariables100 for 2008
        Dim variableDispenser As VariableDispenser
        Try
            variableDispenser = DirectCast(Me.VariableDispenser, VariableDispenser)
            ' Lock the variable for read         
            variableDispenser.LockForRead(varName)
            variableDispenser.GetVariables(vars)

            ' read variable value
            Return vars(varName).Value
        Finally
            If vars IsNot Nothing Then
                ' Unlock the variable         
                vars.Unlock()
            End If
        End Try
    End Function

    Sub WriteVariable(ByVal varName As String, ByVal val As Object)
        Dim vars As IDTSVariables90 '// or IDTSVariables100 for 2008
        Dim variableDispenser As VariableDispenser
        Try
            variableDispenser = DirectCast(Me.VariableDispenser, VariableDispenser)
            ' Lock the variable for write         
            variableDispenser.LockForWrite(varName)
            variableDispenser.GetVariables(vars)

            ' Set the variable         
            vars(varName).Value = val
        Finally
            If vars IsNot Nothing Then
                ' Unlock the variable         
                vars.Unlock()
            End If
        End Try
    End Sub
End Class