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 | Create aggregate shopping statistics depending on store type
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 ==> | Harder than average |
| Subject ==> | SSIS training |
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.
In Management Studio, open and execute the SQL script in the above folder to generate an empty table called WeirdStats. Your mission is to fill this from the Excel workbook of shopping purchases to get this:
For posh shops (Marks and Spencer and Waitrose), show the single most expensive purchase; for cheap shops (Aldi) show the single cheapest purchase; otherwise show the average purchase value.
The table should also show which statistic you've chosen to display, as well as the number of purchases made in that store.
If you're feeling confident, try doing this without reading on to see a suggested flow diagram.
Here's one possible way to solve this:
One possible answer (but not necessarily the best one!).
When you've got this working, close down the package.
In the given solution, there is a single output from aggregate function, then how did you make 3 different aggreagations for Biggest, Normal and Smallest?
With a conditional split transform.
Hi Andy,
The picture shows you have used the conditional splits, once after you aggregated the data.
As per the exercise, we need to show high, low and an average value of sales for three different categories.How do you get the lowest level values after you aggregate the data?
Thank you!
The grouping transform at the start groups by the shop name, and calculates the maximum, minimum and average sales. The conditional split then looks at the shop type, and determines which statistic to display.