Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 24. Separating Data with the Conditional Split Transform

Sometimes you deal with source data that may require different treatments applied to it. For example, you want to generate a mailing list for a direct mail campaign, but you want to target only customers with children. You want to make sure to separate the customers without kids before preparing the list. You would also like anyone who has more than five kids to receive a buy-two-get-one-free coupon with the mailer.

The best way to separate data within a package to apply different types of actions is with the Conditional Split Transform. With this transform you can send data from a single data path to multiple outputs based on conditions set in the Conditional Split Transformation Editor, shown in Figure 24-1. To open the editor drag the transform in the design surface and double-click it.

The Conditional Split Transform uses the SSIS expression language to determine how the data pipeline should be split. If you need a reminder on how the SSIS expression language works, refer back to Lesson 17 where we cover it in more detail. For this example just know that the Conditional Split Transform is checking to see if customers have more than five kids so they can receive the extra coupon. This check will produce three possible outputs:

• For customers with more than five children

• For customers with between one and four children

• For customers with no children

It may look like there are only two outputs but if you look on the bottom ...