SSIS Integration Services | Conditional split transforms exercise | Use a conditional split to count two types of row in a table

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Software ==> SSIS Integration Services  (46 exercises)
Version ==> SSIS 2012 and later
Topic ==> Conditional split transforms  (5 exercises)
Level ==> Relatively easy
Subject ==> SSIS training
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The above folder contains a CSV file of sightings of whales and porpoises by the Hebridean Whale and Dolphin Trust. Create a package called No real porpoise to import this data.

Create two variables as follows:

Variable name What it will be used for
NumberHarbourPorpoises Count the number of harbour porpoises seen
NumberOtherSightings Count the number of sightings of other animals

The aim of this exercise is to create a conditional split with the following rules:

Rule Named path
The Species column is Harbour Porpoise Harbour porpoises
All other sightings Anything else

Create a data flow task which takes the sea life sightings, and splits them into two using the rules above:

The conditional split

Send harbour porpoise sightings down one path, and all other sightings down the other, then use Row Count transforms to count the results.


Add a breakpoint to the PostExecute event of your control flow task:

The task in debug mode

Package execution should pause when this task has finished execution.


Use your Locals window to check the value of your two variables has been set correctly:

The variable values

The values that you should see for each variable.

The harbour porpoise (Phocoena Phocoena) is one of six species of porpoise. It is one of the smallest marine mammals - and also one of the most common, if the figures above are anything to go by!

Stop debugging your package, and close it down.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post