CODEWITHSUNDEEP
ssis
Kannan Karmegam
Kannan Karmegam

Reputation: 661

Finding the last row in SSIS Script Component

I have an SSIS 2008 script component and it uses the method

Input0_ProcessInputRow(Input0Buffer Row)

to process the Rows. Is there a way to find if the Row I'm reading is the Last Row in the Dataset.

Upvotes: 2

Views: 7827

Answers (5)

mavcp10
mavcp10

Reputation: 51

The custom script code is written inside the ScriptMain class. This class inherits from UserComponent which contains a virtual method FinishOutputs(). You can override this in ScriptMain to execute the code you need to run after the last row.

Relevant code in UserComponent.

public override void ProcessInput(int InputID, string InputName, PipelineBuffer Buffer, OutputNameMap OutputMap)
{

    if (InputName.Equals(@"Input 0", StringComparison.Ordinal))
    {
        Input0_ProcessInput(new Input0Buffer(Buffer, GetColumnIndexes(InputID), OutputMap));
    }

    if (Buffer.EndOfRowset)
    {
        InputsFinished = InputsFinished + 1;
        if (InputsFinished == 1)
        {
            FinishOutputs();
            MarkOutputsFinished();
        }
    }

}

public virtual void FinishOutputs()
{
}

Notice that this code is auto-generated and cannot be changed. It does however effectively give you place to put your code as you can override FinishOutputs() in your code within ScriptMain like this:

public override void FinishOutputs()
{
   //do stuff
}

Upvotes: 0

Francis
Francis

Reputation: 361

The solution of overriding InputRows_ProcessInput worked for me. However, don't use the code litterally. The name of the method depends on the name of your Input. In my case the method signature was: 

public override void Input0_ProcessInput(Input0Buffer Buffer)

An easy way to find out the correct method is to go in the script and type "base.Input" intellisense should provide you the correct method to override.

public override void **Input0_ProcessInput**(**Input0Buffer** Buffer)
    {
        while (Buffer.NextRow())
        {
            Input0_ProcessInputRow(Buffer);
        }

        if (Buffer.EndOfRowset()) 
        {
            //do something 
        }
    }

Simply checking for "Buffer.EndOfRowset()" is not sufficient and it doesn't work inside the "while (Buffer.NextRow())" loop. So I guess the "EndOfRowset" occurs after the last row.

Upvotes: 3

user3402637
user3402637

Reputation: 11

Override the InputRows_ProcessInput method as follows:

public override void InputRows_ProcessInput(InputRowsBuffer Buffer)
    {

        while (Buffer.NextRow())
        {
            InputRows_ProcessInputRow(Buffer);
        }

        if (Buffer.EndOfRowset()) 
        {
            //do something 
        }

    }

Upvotes: 1

jlines
jlines

Reputation: 81

if (Row.EndOfRowset()) { // this is the last row }

Upvotes: 8

grapefruitmoon
grapefruitmoon

Reputation: 3008

I've not tried this before, but before your Script Component, add a RowCount Task and set the value of a variable (called RowCnt, initialised to 0 here) in this task. Then in your script, have something like this (ensure the variable declaration is scoped in the Class)...

Public Class ScriptMain
Inherits UserComponent

Dim iRows As Integer

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    iRows = iRows + 1
    If iRows = Variables.RowCnt Then
        'Do something here
    End If
End Sub

End Class

That should allow you to process the final row.

Upvotes: 1

Related Questions