This is part 3 of my 29 part series called Better Know A SSIS Transform. Hopefully you will find the series informative. I will tell you a little about each transform and follow it up with a demo basic you can do on your own.
The Conditional Split provides a way to evaluate incoming rows and separate those rows by an expression your design. After these rows are separated they are sent to different outputs so they can either be cleansed, loaded separately, or detect changing data (a good substitute for the slowly changing dimension). I will provide you some scenarios where you may have to use the conditional split for these reasons and how you would use it. There are of course other possible reasons you may use the Condition Split but these what I typically use it for.
Cleansing Data Example
The scenario is I have a package that loads Company A customers. The data that we receive is not always complete though. Often I will have a zip code for a customer but no city or state. Because this is a known issue the IT department has purchased a zip code extract that list all zip codes and their associated cites and states.
Conditional Split Configuration
Load Data Separate Example
The scenario is I have a package that loads customer mailing lists. Company B sends out promotions and wants to separate those mailing list depending on a customers education level. Those with some college and high school or less education will more likely receive my promotion to attend a career college.
Detecting Changing Data Example
This common scenario is using an alternative method to using the Slowly Changing Dimension. I have incoming records from Company C’s ecommerce system that need to be loaded to my data warehouse. Before these records get loaded I need to check to see if they are either new, updated or duplicate records.