WISE OWL EXERCISES
SSIS INTEGRATION SERVICES EXERCISES
- Data flow tasks (4)
- Basic data transforms (1)
- Data conversion transforms (2)
- Conditional split transforms (5)
- Lookup transforms (4)
- Looping over files (3)
- Looping over rows (2)
- Merge joins (1)
- Previous versions (18)
- Script tasks (1)
- Variables in script (1)
- Script components (2)
- Accessing file attributes (2)
SSIS Integration Services | Previous versions 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 relevant Wise Owl course (sadly, only in the UK for now).
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!):
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:
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:
See below for how the conditional split should work.
The conditional split should have 2 conditions:
|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:
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:
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?