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
    }           
}

Cotopaxi

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. – Foulness

Are you in synchronous mode (default) or did you filp it to asynchronous mode? Was the Script created as a transformation or a destination? – Muckworm

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. – Cotopaxi

Answer 1

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();
}

Cornflower

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) – Bayles

Answer 2

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

Cotopaxi

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. – Bayles

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)? – Cotopaxi

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). – Bayles

How would you then change the rows that have already been fed to the output arrow? You cannot loop through them again. If you wanted to add a new column, how would you do this for all rows with CalculateResults();? How would you code the function if the Row object is no longer in reach? I do not know how you can change a whole column like SQL would do on the output that has already been built. – Mcfarland

Answer 3

You cannot fill the Output0Buffer outside the Input0_ProcessInputRow(Input0Buffer Row) function

But if you want to get the data from SQL queries and feed that to the output, you need to load the SQL output into a DataTable object, work further on that, and then you can get the DataTable's row data for each row that you loop through with the Input0_ProcessInputRow(Input0Buffer Row) function by means of the row indices of the DataTable object. By this, you can do the work that you want to do in the PostExecute() function already before the Input0_ProcessInputRow(Input0Buffer Row) function so that the Script Component output will show the full work.

This does not answer your question of how you can put more rows in the output, but it shows that you can change the output rows as much as you want, and if that can be done, you might also make a Multicast, run the one Script Component as you have it but to its side, run another Script Component with the same row input and change that to your needs so that it outputs all of the rows that you need on top. After this, union the two Output0Buffers to one output by means of the Union tool.

PostExecute() cannot fill the "Output0Buffer" output of the Script Component

Here is how the code would look like in the PostExecute() function, but mind that this is just to show that it does not work. From the default file:

This method is called after all the rows have passed through this component.

The input comes from the SQL query, and the while (reader.Read()) is a loop over the rows, but what I try here cannot work since the Output0Buffer only works in the Input0_ProcessInputRow(Input0Buffer Row):

Thus, this does not work:

    public override void PostExecute()
    {
        base.PostExecute();
        // Output data from the temporary table
        string selectQuery = @"SELECT *
FROM xyz;";
        cmd = new SqlCommand(selectQuery, conn);
        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            Output0Buffer.AddRow();

            // Check if the value is null
            if (!reader.IsDBNull(0))
            {
                // Get the value from the reader and convert it to a string
                string value = reader.GetValue(0).ToString();

                // Set the value in the output buffer
                Output0Buffer.myNewColumn = value;
            }
            else
            {
                // Handle null value as needed
                // For example, you can set it to an empty string
                Output0Buffer.myNewColumn = string.Empty;
            }
        }
    }

The code is taken from SSIS Script Component - How to modify the Output0Buffer.

Mcfarland