How to determine the last row of rowset for further processing during data flow?

Question

I have a problem regarding SSIS 2014 data flow. I need to determine the last row of the rowset during the data flow. I need it for further processing inside the same data flow. Using the script component, I tried the functions NextRow() and EndOfRowset(). However, these functions are not working reliable using the following code:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (!Row.NextRow()) // never true.
    {
        MessageBox.Show("last row");
    }

    if (Row.EndOfRowset()) // never true.
    {
        MessageBox.Show("last row");
    }
}

the reasons are some buffer size issues as you can read here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/b07c5324-56ff-48dc-991c-3947aecf1558/endofrowset-doesnt-work?forum=sqlintegrationservices

So I tried to measure the number of rows so that I could determine the last row. Unfortunately the row count transformation assigns the determined row count to the variable when all rows are processed. So I can't use it during the data flow.

How can I determine the last row during the data flow so that I can process this row individually?

Can you determine it before the dataflow, either by including an "isLast" column in your query output, or doing a COUNT query in a executeSQL task? — Tab Alleman, Aug 19 '14 at 16:56
Hello Tab Alleman, since I didn't found a solution on how to determine the last row during the data flow, I've done it this way using a row count transformation in a previous data flow. That was also suitable to solve my problem. Thanks for help! — 17nxo, Aug 21 '14 at 16:46

score 4 · Answer 1 · answered Aug 24 '14 at 21:26

4

You're going to want to do this in the Input0_ProcessInput method. Try testing out this code in your script component:

public override void Input0_ProcessInput(Input0Buffer Row)
    {
        base.Input0_ProcessInput(Row);

        //Will be true this time
        if (Row.EndOfRowset())
        {
            MessageBox.Show("Last Row");
        }
    }

answered Aug 24 '14 at 21:26

jymbo

1,335
1
15
26

thx for your answer and sorry for my late response. I tried your solution but I can't access my column values of the last row within your if clause. e.g. when I put `MessageBox.Show("last row: " + Row.idColumn)` into the if clause, I get the error **PipelineBuffer has encountered an invalid row index value** altough the Input Column was activated (and accessible within `Input0_ProcessInputRow` method). My objective is to assign the value of `Row.idColumn` to a global variable. – 17nxo Sep 19 '14 at 15:06
1

This worked for me! Didn't realize at first that you're talking about the Input0_ProcessInput function, not Input0_ProcessInputRow, which is what is automatically added to the source code in Script component and for whatever reason, EndOfRowset doesn't work there. So add the function above and you can call your existing ProcessInputRow function (if you want) in a While (Row.NextRow()) loop. Then check EndOfRowset after the While loop completes. – Kelly Jul 11 '17 at 19:28

How to determine the last row of rowset for further processing during data flow?

1 Answers1