SSIS set result set from data flow to variable

Question

Before I give myself some sort of stress related heart attack.

Would anyone know how to complete the seemingly simply task of setting the result set of one data flow task (the result will be either a 0 or 1) and assigning that value to a variable.

I've created the variable ok.

The result set comes from an XML file with multiple elements. The flag (0 or 1) is the result from one of those elements, so I also need to know how to get the result set to be just that flag.

If anyone could help I would really really appreciate it.

Update : I eventually read the result (0,1) back to SQL Server into a flag table. Then used a Execute SQL script to read it back from SQL Server and to a variable. Not sure if this is the best way to have done it but it seems to have done the trick.

Answer 1

You can use a Data Flow Script component to transfer a data flow column value to an SSIS variable. However, you must follow certain rules when working with the Data Flow Script component and SSIS variables.

SSIS doesn't allow you to assign values to SSIS variables in the script procedure that processes rows. But there are pre- and post-execute procedures where you can handle the assignment.

In your Script component, add the SSIS variable to the ReadWriteVariables property. Edit the script and declare a variable in the ScriptMain class. Use the PreExecute procedure to initialize the variable. Use the ProcessInputRow procedure to assign the input -buffer column value to the script variable. And, use the PostExecute task to assign the value from the script variable to the SSIS variable.

Here's an example VB script component. It has an SSIS variable (MyOutVariable) that will get the output of the script variable (MyVar). The MyVar variable gets it's value from the MyNumber column in the data flow.

Public Class ScriptMain
    Inherits UserComponent

    Dim MyVar As Integer

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()

        'initialize variable local to data flow
        MyVar = 0

    End Sub

    Public Overrides Sub PostExecute()
        MyBase.PostExecute()

        ' output variable value to SSIS variable
        Me.Variables.MyOutVariable = MyVar

    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        ' logic to get value
        MyVar = Row.MyNumber

    End Sub
End Class