SSIS: Conditional Split in C#

Question

The flow I have to implement does the conditional splitting according to the last two characters of a column value. For code-maintenance and performance reasons, I need to do the splitting in C#.

How can I code it?

I am OK with the collection part but The buffer part is not clear to me. I do have in Input0_ProcessInputRow

output = ComponentMetaData.OutputCollection["CLEANED_DATA_" + Row.ISO2];

but then for each inut row, I need to do a AddRow to the corresponding output buffer...

How can I do that?

Thank You

`For code-maintenance and performance reasons`! Conditional split is more performant and easier to maintain than implementing the same logic in a script component. — Hadi, May 10 '19 at 11:03
Check the following links (1) http://www.rad.pasfu.com/index.php?/archives/19-How-to-use-Script-Component-as-Asynchronous-Transformation.html (2) https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-scripting-data-flow-script-component-types/creating-an-asynchronous-transformation-with-the-script-component?view=sql-server-2017 — Hadi, May 10 '19 at 11:05
Thanks Hadi. In the provided examples the buffer is MyAddressOutputBuffer ... where the Output is MyAddressOutput ... In my case the Output is dynamically choosen according to input values .... — Bruno C, May 10 '19 at 12:08

J Weezy · Answer 1 · 2019-05-10T15:39:15.797

The components in SSIS are written in C#, so you should get no performance boost going to a script component over a data flow component. I would be interested to know what the benchmark performance difference is between two identical packages that implement the conditional split via the Conditional Split component vs a Script Component. You may have a performance bottleneck elsewhere.

You mention that splitting is handled dynamically, can you please be more specific on what the rules are for that? Typically, row splitting can be handled with a Derived Column transformation that can create a new column called RowSplitIndicator that can be a bit, int, string value. From there, the row can be Conditionally Split based on whatever value is in the RowSplitIndicator. This simplifies the overall design in the event that the split logic is complex.

The answer to your question, however, is that you first need to add a row to the pipeline buffer and then you can assign values to the buffer's columns:

MyAddressOutputBuffer.AddRow();
MyAddressOutputBuffer.OutputColumnName = YourVariable;

Note: your script component is now asynchronous in the sense that there is one row in and there are many rows out.

score 0 · Answer 2 · answered May 10 '19 at 23:31

You mentioned that:

For code-maintenance and performance reasons. I need to do the splitting in C#

Conditional split is more performant and easier to maintain than implementing the same logic in a script component especially that you need to implement an asynchronous logic.

Anyway, to do that using a script component, you can simply use a switch statement to do that:

switch (Row.ISO2){

    case "Value1":
        Output1Buffer.AddRow();
        Output1Buffer.Column = Row.Column;
        break;


    case "Value2":
        Output2Buffer.AddRow();
        Output2Buffer.Column = Row.Column;
        break;


    case "Value3":
        Output3Buffer.AddRow();
        Output3Buffer.Column = Row.Column;
        break;

    default:
        Output4Buffer.AddRow();
        Output4Buffer.Column = Row.Column;
        break;
}

Make sure that all output don't have a Synchronous Input and that this property is set to None.

SSIS: Conditional Split in C#

2 Answers2