SSIS Script Component - How to modify the Output0Buffer

Question

I've a script component which accepts records from SQL Azure database table. The script then invokes a web service, which returns the number of failed and success records.

For all the records, I would like to add the Status field which has either "success" or "fail" and this gets output from script component.

I then log those output into a text file.

Problem: I'm not been able to add status for each input records since the web service call only happens on post execute.

I tried this but still doesn't work:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData 
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime

         });
 }

 public override void CreateNewOutputRows()
 {
     MessageBox.Show("Test CreateNewOutputRows");
     MessageBox.Show(listOfData.Count.ToString());
     foreach (var item in listOfData)
     {
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
     }
 }

Why can you not execute the web service call during the processinputrows? All the data has already gone down the pipeline once it's out of that method. You are accumulating data in your local variable but at this point it's like holding a picture of a car that just drove away-yes, that's what it looked like but it's not coming back — billinkc
– billinkc, Commented Oct 23, 2011 at 14:58

JohnDavid · Accepted Answer · 2013-07-08 20:14:45Z

Everything in a Transformation is done within Input0_ProcessInputRow The solution would essentially be

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
         listOfData.Add(new ClockData 
         {
             TimeClockID=Row.TimeClockID,
             PersonID=Row.EmployeeCode,
             LocationCode=Row.ClockInServiceContextID,
             ClockInDateTime=Row.ClockInDateTime,
             ClockOutDateTime=Row.ClockOutDateTime
         });
         Output0Buffer.AddRow();
         Output0Buffer.EmployeeCode = item.PersonID;
         MessageBox.Show(item.PersonID);
}

You may be able to perform the output steps in PostExecute, CreateNewOutputRows is not run in Transformation, only in Destination scripts.

Marian · Accepted Answer · 2011-10-23 11:42:41Z

I'm not very proficient in SSIS, but I think that you could try the following ideas:

go to the script component and edit it,
go to section Inputs and Outputs (3rd section),
go to Output0 - Output columns,
add a new column (give it a name and type, let's say Status - boolean).

Then you'd have the empty column to your output and you will need to populate it with some data for each of the input rows:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Row.Status = IIf(String.IsNullOrEmpty(rowValues.GetValue(1).ToString()), 0, 1)

This will help you add a Status column to your output table. Hope this is what you wanted.

questionto42 · Accepted Answer · 2024-04-07 22:18:30Z

1. Output0Buffer.myNewColumn and `SynchronousInputID`: `None` only if you want to drop all input columns and pass any new columns

You can only set the "Output0Buffer" if you drop the input columns from the Script Component output. That means, if you make a new column from the input columns and this new column shall be passed downstream, then you need "Output0Buffer". See the other answer here for more.

In the "Inputs and Outputs" -> "Output 0" (or what you call it) -> "Common Properties" -> SynchronousInputID: None:

If you draw an output arrow to a next item and double click the arrow, you can see that just one column (the one column that I put in "Output 0") is in the output:

Without this setting as None, you will not be able to deal with the "Output0Buffer" in the code at all.

Input0_ProcessInputRow(Input0Buffer Row)

Mind that you cannot run a reader inside this function since the function itself is already a reader. If you do, it will throw:

[my component name [104]] Error: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Thus, this function is itself a loop over the rows of the input data that reaches the Script Component. You cannot feed it with the live SQL query output or anything else. And this loop is done over a DataReader object. From the default file, we get this help:

/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
///  string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
///  Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Output0Buffer.AddRow();
        string value = "abc";
        // Set the value in the output buffer
        Output0Buffer.myNewColumn = value;
    }

Checking the output with a Data Viewer:

Makes:

There are 100 rows since the input has 100 rows, and the code only sets the new column with its new value. It could also be a sum of two input columns or the like. The myNewColumn is the only column that is passed to the output of the Script Component.

For a longer code with a loop over all input columns and a mapping to a data table that in the end gets bulk inserted to a temporary table, see How do I create and fill a temporary table from a data source by means of a C# DataTable without ever leaving the SSIS Data Flow Task?.

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

Here is how the code could 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;
            }
        }
    }

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

Fix: PreExecute() to fill Input0_ProcessInputRow(Input0Buffer Row)

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, and then you can add the DataTable's row data in each Row of the Script Component output with Input0_ProcessInputRow(Input0Buffer Row) by means of the row indices of the DataTable object. You choose whether you want to put it to the given input data as a new column with Row.myNewColumn or whether you want to put it in the Output0Buffer.myNewColumn while dropping the input data. You can only do one of the two and then loop through all Row objects for the full output.

2. Row.myNewColumn and `SynchronousInputID`: not `None` if you want to keep the input columns and pass them together with the new output columns in each `Row`

If you instead want to pass all input columns together with the new output column, then you need to get the new column value inside each row, which is said in this other answer.

In the "Inputs and Outputs" -> "Output 0" (or what you call it) -> "Common Properties" -> SynchronousInputID: not None:

And if you click on the output arrow, the metadata has all of the input columns and the one new output column at the bottom:

If you do not set the setting to None and still ask for the "Output0Buffer", intellisense will underline it red and will throw the Error CS0103:

The name 'Output0Buffer' does not exist in the current context

See SSIS Script transformation error CS0103 when using Output0Buffer class - Stack Overflow and Compiler Error CS0103 - Microsoft Learn for more.

Stack Exchange Network

SSIS Script Component - How to modify the Output0Buffer

3 Answers 3

1. Output0Buffer.myNewColumn and `SynchronousInputID`: `None` only if you want to drop all input columns and pass any new columns

Input0_ProcessInputRow(Input0Buffer Row)

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

Fix: PreExecute() to fill Input0_ProcessInputRow(Input0Buffer Row)

2. Row.myNewColumn and `SynchronousInputID`: not `None` if you want to keep the input columns and pass them together with the new output columns in each `Row`

Your Answer

Linked

Hot Network Questions

SSIS Script Component - How to modify the Output0Buffer

3 Answers 3

1. Output0Buffer.myNewColumn and SynchronousInputID: None only if you want to drop all input columns and pass any new columns

Input0_ProcessInputRow(Input0Buffer Row)

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

Fix: PreExecute() to fill Input0_ProcessInputRow(Input0Buffer Row)

2. Row.myNewColumn and SynchronousInputID: not None if you want to keep the input columns and pass them together with the new output columns in each Row

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions

1. Output0Buffer.myNewColumn and `SynchronousInputID`: `None` only if you want to drop all input columns and pass any new columns

2. Row.myNewColumn and `SynchronousInputID`: not `None` if you want to keep the input columns and pass them together with the new output columns in each `Row`