SSIS Expression Language and the Conditional Split Transformation

Introduction

This post is part of a series titled An Introduction to the SSIS Expression Language.

In this post, I demonstrate the SSIS Expression Language and the Conditional Split Transformation.

To build the demo project described in this article you will need SSIS 2005 and the AdventureWorks sample database (which can be downloaded at http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004).

Build The Demo Project

If you have not already done so, create an SSIS project named ExpressionLanguageDemo.

Once the project is created, open Solution Explorer and rename Package.dtsx – change the name to ConditionalSplit.dtsx. If you’ve already created the project, right-click the SSIS Packages folder in Solution Explorer and click New Package – and rename that package to ConditionalSplit.dtsx. When prompted, rename the package object as well. (I think you should always do this – I always answer this message box by clicking the Yes button.)

Drag a Data Flow Task onto the Control Flow and click the Data Flow tab to edit. Drag an OLE DB Source onto the canvas. Double-click the OLE DB Source to open the editor. As in the post entitled SSIS Expression Language and Variables, click the New button to create a new Connection Manager to the AdventureWorks database.

Set the Data Access Mode to SQL Command and enter the following T-SQL statement into the SQL Command Text textbox:

Select
  ContactID
 ,Title
 ,FirstName
 ,MiddleName
 ,LastName
 ,EmailAddress
From Person.Contact

Click OK to close the editor.

On One Condition…

Drag a Conditional Split transformation onto the Data Flow canvas and connect the OLE DB Source Adapter to it:

Double-click the Conditional Split to open the editor. Expand the NULL Functions folder in the Operations list (upper right). Drag an ISNULL function into the Condition column of the first row in the Conditions grid. Expand the Columns folder in the Values list (upper left). Click and drag the Title column from the list onto the <<expression>> placeholder in the ISNULL expression:

The default name for a Condition is “Case n” where n is the row number of the Condition in the Condition grid.

Next, drag and drop the ContactID column into the second row’s Condition column. Complete the expression so that it reads: ContactID <= 5000. Rename the Outputs to NullTitles and SmallContactIDs respectively:

What we’ve done here is define a couple of outputs. One of the outputs will contain rows where the Title is NULL. The other will contain – this is important, pay attention – rows where the Title is not NULL and the ContactID is less than or equal to 5000. Why is this? It’s because rows with NULL Titles are redirected to the NullTitles output first. The ContactID value of these rows is never evaluated to see if it’s less than or equal to 5000. If neither condition applies to a row, that row is sent to the Conditional Split Default Output. This operates a lot like a Switch statement in C# or a Select Case statement in VB, with the Default Output acting like the Else branch. You can adjust the order of condition evaluation using the spinner buttons on the right:

Click OK to close the Conditional Split editor.

Terminate It! 

Drag a Multicast transformation onto the Data Flow task surface. We’ll use the Multicast to terminate a Data Flow Path. Drag a Data Flow Path from the Conditional Split transformation to the Multicast. When you do this, you’ll be prompted to select an output from the Conditional Split to connect to the Multicast input:

After selecting an Output, click Ok to close:

The title of the output you selected appears in the label.

Conclusion

 Expressions are used to branch data row flow inside the Data Flow Task with the Conditional Split transformation.

:{> Andy 

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS guy, and farmer. I was cloud before cloud was cool. :{>

11 thoughts on “SSIS Expression Language and the Conditional Split Transformation

  1. Really easy understanding…Andy i need help in Deployment of packages in other machines or remote servers, can you share the link

  2. Andy,
    Lets say you wanted to send these outputs to an actual table but needed to do a data conversion on non null values and the ones with null values you want to go directly to table. How would you go about this. I know the path for the data conversion but I don’t know how I get the rows that had null to this table. Your help is much appreciated!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.