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
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.

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:

Statistics on shopping

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:

A possible answer

One possible answer (but not necessarily the best one!).

When you've got this working, close down the package. 

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 1 thread Add post
28 Aug 19 at 11:21

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?

28 Aug 19 at 13:23

With a conditional split transform.

27 Nov 19 at 04:32

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!

Andy B  
27 Nov 19 at 09:10

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.