SSIS: Conditional Split Transformation

Integrating conditional logic into the data pipeline.

Many projects requires the use of conditional logic to enforce logic according to business rules.
In this example, I’ll illustrate such logic by diverting records between one of two Excel spreadsheets depending on whether each Employee is salaried.
First, I’ll drag and configure an OLE Db Source object to connect to the AdventureWorks.HumanResource.Employee table.

1-conditional-split

Next I’ll drag a Conditional Split transformation and connect the OLE Db Source object’s green arrow to it.

2-conditional-split

Now I’ll configure the transformation to test the values for each row’s Salaried field.
Also, I assigned an intuitive name for each branch of logic for easier future reference.

3-conditional-split

Now I’ll drag two Excel Destination objects unto the designer and connect two green lines from the transformation to each Excel spreadsheet.

4-conditional-split

For each Excel document, I’ll configure it by creating a new Destination Manager, creating a new “Table or View,” then Mapping the columns.

By executing the package, I know have two Excel documents, each containing salaried and non-salaried employees.

6-conditional-split

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s