Exercise: Use a conditional split to divide up scary rollercoasters

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.

The answer to the exercise will be included and explained if you attend the Wise Owl course listed below!

Category ==> SSIS Integration Services  (18 exercises)
Topic ==> Expressions  (2 exercises)
Level ==> Average difficulty
Course ==> Integration Services
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 aim of this exercise is to create the following data flow task (help follows!):

Full diagram of conditional split

Here the task has paused because we're displaying a data viewer for the last step.

Here is what the final data viewer should be showing:

Data viewer of coasters

The start of the data viewer (we've edited it to just show 5 columns, but the order of rows may be different for your package).


To achieve this, first create a package called Coasters.

Create a data flow task which starts with an Excel source linked to the workbook in the folder above, followed by a Conditional Split transform:

The first two tasks

See below for how the conditional split should work.


The conditional split should have 2 conditions:

Output name Condition
Very scary The Speed column is more than 50.
Quite scary Either the coaster's Type column is Wood, or the Design column is not Sit Down.
Not scary Any other coasters failing the first two conditions (the default output).

Be very careful to make sure that you're not making any assumptions about the case of text!

Now add 3 derived column transforms, each of which should create a column called Scariness to hold the values Very scary, Quite scary and Not scary respectively:

A sample derived column

Note that we've converted the data type to 20 characters, otherwise we will have trouble merging the 3 Scariness columns in the next step.

Finally, add a couple of Union All transforms:

Union all transforms

You'll need to configure the input/output columns carefully for the first transform.  The only purpose of the second one is to allow us to show a data viewer for the final set of data.


Run your package and test that it works. 

Final thought: was this really the quickest way to accomplish this?

This page has 0 threads Add post