Increment Variables in Data Flow task

Question

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.

score 2 · Accepted Answer · answered Sep 29 '19 at 17:38

2

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

answered Sep 29 '19 at 17:38

Hadi

36,233
13
65
124

Thanks for this - would I be able to use the local variable outside of the script? I need to be able to take that value calculated and have it as a foreign key on another split record. – JbP Sep 30 '19 at 06:48
@JbP No you cant. You have to assign this value to a SSIS variable in PostExecute method. As i mentioned in the code above – Hadi Sep 30 '19 at 07:08
Yeah sorry mate, did read that but slightly stressing with this at the moment :) I basically have a file which will have a number of AAA records, each one should have its own ID, then under that I have a BBB record which will also need to have the relevant AAA ID written to it. – JbP Sep 30 '19 at 07:10
I am just at a loss on how to do it. – JbP Sep 30 '19 at 07:10
1

@JbP I think you should use a **Lookup Transformation** or a **Merge Join** to get the relevant ID for the second source. There are a lot of examples online – Hadi Sep 30 '19 at 07:21
Your a star mate - thank you so much for your help. – JbP Sep 30 '19 at 07:58

Increment Variables in Data Flow task

1 Answers1