how to skip from a row in script component

Question

How to skip some records in script component without using conditional split component?

Answer 1

To skip records in a script component, you need to create the script component with asynchronous outputs. By default, a script component uses synchronous output, which means that each and every row that is input to the script will also be an output from the script.

If you're using SQL Server 2005, I think you'll have to start with a new Script component, because you can't change from synchronous to asynchronous once you've worked with a Script component. In SSIS for SQL Server 2008 you can switch a Script component from synchronous to asynchronous.

Edit your Script component and select the Inputs and Outputs tab. Select the Output buffer in the treeview. Select the SynchronousInputID property and change the value to None. Select the Output Columns branch in the treeview. You must use the Add Column button to create a column for each input column.

Now you can edit your script. In the procedure that processes the rows, you will add some code to control skipping and outputting rows. When you want to skip a row, you will use the Row.NextRow() command where Row is the name of the input buffer. Here's an example:

    If Row.number = 5 Then
        Row.NextRow()
    End If

In this example rows that have a 5 in the number column will be skipped.

After applying your other transformation logic, you need to indicate that the row should go to the output. This is initiated with the Output0.AddRow() command where Output0 is the name of the output buffer. The AddRow function creates the next output buffer, which pushes the previous row out of the Script component. After you create the new row, you must assign values to the columns in the new row.

    Output0Buffer.AddRow()
    Output0Buffer.number = Row.number

This example adds a new row to the buffer and assigns the number value from the input buffer to the number column in the output buffer.