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.
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