Can I add rows to Output Buffer in SSIS Script Component in PostExecute?

Question

I have a Script Component where I buffer all the rows, then do some processing, and then I want to create the output rows. I tried accessing the Output Buffer object in PostExecute but apparently that's not possible? Gives "Object Reference not set to an instance of an object" error when it hits AddRow(). Is there a way to do this?

 public override void PostExecute()
{
    base.PostExecute();

    //processing

    foreach(ChartValue cv in chartValues)
    {
        Output0Buffer.AddRow();
        Output0Buffer.usedcl = cv.Centerline;
        //etc
    }           
}

Yes, you only have access to output rows in `Input0_ProcessInputRow(Input0Buffer Row)`. This might seem like a problem, but always there are ways around it. I couldn't quite understand what you are going to do in your code. — TheEsnSiavashi, Jun 28 '17 at 15:49
Are you in synchronous mode (default) or did you filp it to asynchronous mode? Was the Script created as a transformation or a destination? — billinkc, Jun 28 '17 at 16:27
It's Transformation and it's in asynchronous mode. There could be a way around it, it would just be so much easier if I could add to the output from PostExecute. Maybe if I get a count of the rows beforehand, I suppose I would know in ProcessInputRow that I'm on the last row and then do the final processing. — Kelly, Jun 28 '17 at 16:45

score 3 · Answer 1 · answered Jul 28 '17 at 14:53

3

The answer is no, you can't do that but easy solution: add the ProcessInput function to loop through each row using the ProcessInputRow function (the function that is already included) and use EndOfRowset to know when you are done, then you can do the final processing code.

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

    try
    {
        //loop through each row
        while (Buffer.NextRow())
        {
             Input0_ProcessInputRow(Buffer);
        }

        //when done collecting all rows, do calculations
        if (Buffer.EndOfRowset())
        {
            CalculateResults();
        }
    }
    catch (Exception e)
    {
       //code here
    }
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{ 
     //gather each row's values and put into List for processing at the end

answered Jul 28 '17 at 14:53

Kelly

945
2
18
31

2

This code should work, but I believe you don't need to loop through each row if you call `base.Input0_ProcessInput(Buffer);`. Just `if (Buffer.EndOfRowset()) { CalculateResults(); }` will suffice after calling the base method. This code also works, but your while loop will never do any of the row processing. – H B Apr 03 '18 at 16:33
Are you saying you don't think the while loop isn't doing anything? It's just skipping over it (not finding a next row)? – Kelly Apr 03 '18 at 16:38
Yes, Exactly. Because the Buffer contents have already been processed entirely by `base.Input0_ProcessInput(Buffer);` This base method internally also calls `Input0_ProcessInputRow` for each row so it's hard to tell the difference (without debugging). – H B Apr 04 '18 at 12:43

score 0 · Accepted Answer · edited Jun 22 '18 at 09:05

0

Thanks Kelly for your example. But like H B said the base.Input0_ProcessInput() calls Input0_ProcessInputRow for every row. And in my case Buffer.EndOfRowset() is false always. So I make the shorter code:

public override void Input0_ProcessInput(Input0Buffer Buffer)
{
    base.Input0_ProcessInput(Buffer); // operate rows in while loop

     //when done collecting all rows, do calculations
    CalculateResults();
}

edited Jun 22 '18 at 09:05

Derrick

3,669
5
35
50

answered Jun 22 '18 at 05:06

Aydar

16

This is not entirely correct. `Input0_ProcessInput` can be called multiple times, you must include the check for `Buffer.EndOfRowset()`. (for small datasets this will do just fine though) – H B Dec 20 '18 at 15:28

Can I add rows to Output Buffer in SSIS Script Component in PostExecute?

2 Answers2

Linked