CODEWITHSUNDEEP
sql-server-2005ssis
dcpartners
dcpartners

Reputation: 5446

How to Pass Variables into Script Transformation Editor?

I would like to pass Variables from my package into Script Transformation Editor. How do I add this into my Row.Reference1 below?

Here's the script that I use:

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

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Row.Column1 = Tokenise(Row.Column0, "|", 1)
        Row.Column2 = Tokenise(Row.Column0, "|", 2)
        Row.Column3 = Tokenise(Row.Column0, "|", 3)
        Row.Column4 = Tokenise(Row.Column0, "|", 4)
        Row.Column5 = Tokenise(Row.Column0, "|", 5)
        Row.Column6 = Tokenise(Row.Column0, "|", 6)
        Row.Column7 = Tokenise(Row.Column0, "|", 7)
        Row.Column8 = Tokenise(Row.Column0, "|", 8)
        Row.Column9 = Tokenise(Row.Column0, "|", 9)
        Row.Column10 = Tokenise(Row.Column0, "|", 10)
        Row.Column11 = Tokenise(Row.Column0, "|", 11)
        Row.Column12 = Tokenise(Row.Column0, "|", 12)
        Row.Column13 = Tokenise(Row.Column0, "|", 13)
        Row.Column14 = Tokenise(Row.Column0, "|", 14)
        Row.Column15 = Tokenise(Row.Column0, "|", 15)
        Row.Column16 = Tokenise(Row.Column0, "|", 16)
        'Row.Reference1 = ???

    End Sub

Private Function Tokenise(ByVal input As String, ByVal delimiter As String, ByVal token As Integer) As String

        Dim tokenArray As String()
        tokenArray = input.Split(delimiter.ToCharArray) 'Split the string by the delimiter
        If tokenArray.Length < token Then 'Protect against a request for a token that doesn't exist
            Return ""
        Else
            Return tokenArray(token - 1)
        End If
    End Function
End Class

Upvotes: 0

Views: 1405

Answers (1)

John Saunders
John Saunders

Reputation: 161773

On the "Script" page of the Script Transformation Editor, the last section is "Custom Properties". There are two properties: ReadOnlyVariables and ReadWriteVariables. Set those that are appropriate to a comma-separated list of the variables you want the script to reference.

When you click the "Edit Script" button, you'll find that the Variables class created in the ComponentWrapper.vb now has properties with the names of the variables. The read-only variables will exist as readonly properties; the ReadWrite variables will exist as properties with both a getter and a setter.

You then reference them as, for instance, Variables.TaskID.

Upvotes: 1

Related Questions