Using the Script Component as a Conditional Split
This is a quick walk through on how you can use the Script Component to perform Conditional Split like behaviour, splitting your data across multiple outputs. We will use C# code to decide what does flows to which output, rather than the expression syntax of the Conditional Split transformation. Start by setting up the source. For my example the source is a list of SQL objects from sys.objects, just a quick way to get some data: SELECT type, name FROM sys.objects
type
name
S
syssoftobjrefs
F
FK_Message_Page
U
Conference
IT
queue_messages_23007163
Shown above is a small sample of the data you could expect to see.
Once you have setup your source, add the Script Component, selecting Transformation when prompted for the type, and connect it up to the source.
Now open the component, but don’t dive into the script just yet. First we need to select some columns. Select the Input Columns page and then select the columns we want to uses as part of our filter logic. You don’t need to choose columns that you may want later, this is just the columns used in the script itself.
Next we need to add our outputs. Select the Inputs and Outputs page.You get one by default, but we need to add some more, it wouldn’t be much of a split otherwise. For this example we’ll add just one more. Click the Add Output button, and you’ll see a new output is added. Now we need to set some properties, so make sure our new Output 1 is selected. In the properties grid change the SynchronousInputID property to be our input Input 0, and change the ExclusionGroup property to 1.
Now select Ouput 0 and change the ExclusionGroup property to 2. This value itself isn’t important, provided each output has a different value other than zero. By setting this property on both outputs it allows us to split the data down one or the other, making each exclusive. If we left it to 0, that output would get all the rows. It can be a useful feature allowing you to copy selected rows to one output whilst retraining the full set of data in the other.
Now we can go back to the Script page and start writing some code. For the example we will do a very simple test, if the value of the type column is U, for user table, then it goes down the first output, otherwise it ends up in the other. This mimics the exclusive behaviour of the conditional split transformation.
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
// Filter all user tables to the first output,
// the remaining objects down the other
if (Row.type.Trim() == "U")
{
Row.DirectRowToOutput0();
}
else
{
Row.DirectRowToOutput1();
}
}
The code itself is very simple, a basic if clause that determines which of the DirectRowToOutput methods we call, there is one for each output. Of course you could write a lot more code to implement some very complex logic, but the final direction is still just a method call.
If we now close the script component, we can hook up the outputs and test the package.
Your numbers will vary depending on the sample database but as you can see we have clearly split out input data into two outputs.
As a final tip, when adding the outputs I would normally rename them, changing the Name in the Properties grid. This means the generated methods follow the pattern as do the path label shown on the design surface, making everything that much easier to recognise.
