Using the Script Component as a Conditional Split

by Darren Green 12 Nov 2010 13:46

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.

image

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.

image

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.

image

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.

image

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.

image

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.

Comments (2) -

11/26/2010 9:29:41 AM #

Mohan

Hi Darren,

That was an amazing article.

I was hungry for more to hear from you that I have similar set up, just OLEDB source in your example would be replaced with .CSV file source.

I would need to read the .csv file as input source -> Script component read-> match to OLEDB destination1 and unmatched to other OLEDB destination2.

Kindly do let me kow your suggestions on this with low latency.


Regards,
Mohan

Mohan India

12/6/2010 9:23:04 AM #

Darren Green

Mohan, I'm not clear on what the problem you're having is. Seems like you should be able to just build the data flow as you describe. If you want more help for your own solution, perhaps try asking on the MSDN SSIS Forum social.msdn.microsoft.com/.../threads.

Darren Green United Kingdom

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS