SSIS Script Component Write to Variable

Question

This is with SQL 2005.

I have a script component inside a Data Flow Task. I would like to read from the input columns and write the data to a global user variable.

I've set my input columns and added my global user variable as a ReadWriteVariable to the script component properties.

Here is my code, I'm just trying to alter the value of the global user variable here, but its not working. When I write out the value of the variable in another task it still has its default value:

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

Public Class ScriptMain
    Inherits UserComponent
    Dim updateSQL As String

    Public Sub Main()
        Dim vars As IDTSVariables90

        VariableDispenser.LockOneForWrite("SQL_ATTR_Update", vars)
        vars("SQL_ATTR_Update").Value = "Test"
        vars.Unlock()
    End Sub


    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        'updateSQL = Row.ITMID + Row.PRCCAT
    End Sub

End Class

I have also tried with no luck:

Me.ReadWriteVariables("SQL_ATTR_Update").Value = "Test" 

Answer 1

I figured it out.

From MS:

In Script component code, you use typed accessor properties to access certain package features such as variables and connection managers.

The PreExecute method can access only read-only variables. The PostExecute method can access both read-only and read/write variables.

For more information about these methods, see Coding and Debugging the Script Component.

http://msdn.microsoft.com/en-us/library/ms136031.aspx

It looks like Dts is only available in Script Task.

Here is what the code looks like:

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

Public Class ScriptMain
    Inherits UserComponent
    Dim updateSQL As String


    Public Overrides Sub PostExecute()
        Me.ReadWriteVariables("SQL_ATTR_Update").Value = "Test"
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        'updateSQL = Row.ITMID + Row.PRCCAT
    End Sub

End Class

Answer 2

Here is how you could do it: http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html