21 Mar

SSIS: Conditional Split

The Conditional Split transformation is relatively clean, logically.  You take an expression, set a condition(s) and the result set is split based on the condition(s).  Lets get started!

As an aside, I finally figured out that I should copy this section.  It’s only taken me 10 posts to realize I use this same example every time.

Step 1: Create a OLE DB data source.

  1. Create a new package and call it whatever you like.
  2. Create a Data Flow task by dragging it to the Control Flow or just clicking the Data Flow tab and clicking the link there.
  3. Drag a OLE DB data source to the data flow.
    1. Point it to the AdventureWorks2012 database, or whatever you have available.  Create a new connection if you need to.
    2. Data access mode: Table or view.
    3. Name of the table or view: [dbo].[salesorderheader].
    4. Click OK to exit.

Step 2: Create a Conditional Split transformation.

  1. Drag a Conditional Split transformation to the Data Flow and connect the OLE DB data source to it.
  2. Double-click the Conditional Split transformation and configure it as follows:



That is certainly easier than writing it out.  Click OK to exit.

Step 3: View output data.

Dastardly plan.

Dastardly plan.

Drag four UNION ALL transformations to the desktop.  Name them 2005, 2006, 2007, 2008.  Connect the Conditional Split to them.  When you connect the Split to the Union ALL you will be given the option to map an output to the path.  I’m going to run under the theory that you can figure out my dastardly plan and connect the yr2005 to the 2005 UNION ALL.

We won’t bother with a data viewer because of the size of the transformation but if you run the package it should look like this.