Finding the last row in SSIS Script Component

I have an SSIS 2008 script component and it uses the开发者_JS百科 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.

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

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.

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.

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 
        }

    }

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
}