CODEWITHSUNDEEP
sql-servervb.netssisetlscript-component
JbP
JbP

Reputation: 45

Increment Variables in Data Flow task

I have a large text file which has multiple different rows within it.

I am using a Conditional Split which looks at the row type (Field 1) and then takes an action, mostly trying to increment a variable, split the single row into multiple columns (Derived) and then write the results to a table.

However, when trying to increment the variable, I am getting "The collection of variables locked for read and write access is not available outside of PostExecute."

The variable is being updated with a script component.

I have tried moving the code to the PostExecute however at that point, it never seems to increment.


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim MaximumKey As Int32 = Me.Variables.SPIParentID ' Grab value of MaxKey which was passed in

    ' NextKey will always be zero when we start the package.
    ' This will set up the counter accordingly
    If (NextKey = 0) Then
        ' Use MaximumKey +1 here because we already have data
        ' and we need to start with the next available key
        NextKey = MaximumKey + 1
    Else
        ' Use NextKey +1 here because we are now relying on
        ' our counter within this script task.
        NextKey = NextKey + 1
    End If

    'Row.pkAAAParentID = NextKey ' Assign NextKey to our ClientKey field on our data row
    Me.Variables.SPIParentID = NextKey
End Sub

I would like to be able to loop through the file using the conditional split I have in place, then when it reached a certain record type it will take the current RecordTypeID and increment it to then write out to the next record.

Upvotes: 2

Views: 1094

Answers (1)

Hadi
Hadi

Reputation: 37313

SSIS variables value cannot be changes within a data flow task, the value is changed after the whole Data Flow Task is executed. Anytime you try to read the value from this variable it will return its value when the data flow task is executed. You can change use a local variable within the script to achieve the same logic:

Dim MaximumKey As Int32 = 0 

Public Overrides Sub PreExecute()

    MyBase.PreExecute()

    MaximumKey = Me.Variables.SPIParentID ' Read the initial value of the variable

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    ' NextKey will always be zero when we start the package.
    ' This will set up the counter accordingly
    If (NextKey = 0) Then
        ' Use MaximumKey +1 here because we already have data
        ' and we need to start with the next available key
        NextKey = MaximumKey + 1
    Else
        ' Use NextKey +1 here because we are now relying on
        ' our counter within this script task.
        NextKey = NextKey + 1
    End If

    'Row.pkAAAParentID = NextKey ' Assign NextKey to our ClientKey field on our data row
    Me.Variables.SPIParentID = NextKey
End Sub

Public Overrides Sub PostExecute()

    MyBase.PostExecute()

    Me.Variables.SPIParentID = MaximumKey ' Save the latest value into the variable

End Sub

Upvotes: 2

Related Questions