EXERCISE TOPIC▼
SSIS 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 | 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.
You can learn how to do this exercise if you attend one of more of the courses listed below!
| Software ==> | SSIS Integration Services (46 exercises) |
| Version ==> | SSIS 2012 and later |
| Topic ==> | Conditional split transforms (5 exercises) |
| Level ==> | Relatively easy |
| Courses ==> | Introduction to SSIS / Fast track SSIS |
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:
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:
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 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.