CODEWITHSUNDEEP
sql-server-2008ssisscript-component
Jai
Jai

Reputation: 33

SSIS: Remove Empty Row from outputbuffer

I have an error on a data flow task.

i have an ado.net source going to an async script component.

the problem comes at the very end of the insert from the script component to the destination where it

tries to put the empty row it automatically puts in the output buffer in my table.

Is there a way to prevent this from happening/ how can i manipulate the output buffer so i can remove specific rows from it?

Upvotes: 0

Views: 4504

Answers (3)

The Blue Nova
The Blue Nova

Reputation: 11

The Problem seems to be SSIS executing the AddRow() Function during PreExecute. This then tends to clash with any use of AddRow() in your public override void ProcessInputRow() Function. e.g. PreExecute makes a new row to kick things off and then you manually create a new row as a part of your processing, you now have 2 new rows, the first of which is full of NULLS and already passed to the outputbuffer. To fix this i override CreateNewOutputRows() and leave it empty, than only add rows as i need, e.g. some of my asynchronous scripts never add a row at all to the output buffer if the correct data doesn't show up on the input buffer.

public class ScriptMain : UserComponent {

    public override void CreateNewOutputRows() {}

    public override void Input_ProcessInputRow(InputBuffer Row) {
       if(asNeeded) {
          OutputBuffer.AddRow();
       }
    }
}

Upvotes: 0

Mike
Mike

Reputation: 11

To solve this issue, I added the conditional split component and added a length check on one of the blank columns (i.e len([testfield])>0). I then sent those fields with that check to the database and did nothing for the other branch.

Upvotes: 1

bobs
bobs

Reputation: 22184

You say that the source goes to an async script component. If this is true, then you are already controlling when rows go to the output buffer.

By default, a script component is synchronous. There is one output row for each input row. Maybe this is what you currently have setup.

If you want to use a script component as an asynchronous component, you have to do the following:

  • Add a new script component
  • Right-click on the component and select Show Advanced Editor...
  • On the Input and Output Properties, select the Output item and change the SynchronousInputID value to None
  • On the Output tab, add columns that will define the output buffer. You must define the columns because, unlike with a synchronous script, the output buffer is not automatically defined as a match to the input buffer.

When you edit the script, you can use the OutputBuffer.AddRow method to create new rows in the output buffer. Each time you call AddRow, the existing buffer content is sent out of the script component and cleared for the next row. Using your rules for skipping rows, you will validate the input buffer before adding or skipping the row.

Upvotes: 1

Related Questions