SSIS 2008 - Aggregate Transformation

Aggregate Transformation:

As per the name ‘Aggregate’ is a data manipulation transformation which provides several aggregation operations and a group by feature. This transformation allows multiple simultaneous aggregations on multiple columns, as well as multiple aggregations on same column. This transformation provides one or more outputs. It’s Asynchronous transformation which does not support ‘Error output’. Listed below are the various aggregation operations provided by this transformation:

Operation	Description
Group By	Based on the columns selected for grouping, it divides the given dataset into Groups.
Sum	Provides the SUM of the selected column. Note: only numeric datatype columns can be used for this operation.
Average	Provides the Average of the selected column. Note: only numeric datatype columns can be used for this operation.
Count	Returns the number of items present in a given group
Count distinct	Returns the unique, not null number of items present in a given group
Minimum	Provides the minimum value present in the selected column. Note: only numeric, date & time datatype columns can be used for this operation.
Maximum	Provides the Maximum value present in the selected column. Note: only numeric, date & time datatype columns can be used for this operation.

Below is the sample dataset on which we will try to perform the above mentioned Aggregate functions:

SalesRep_Id	Month	Year	Region	Country_Code	Unit_Sold
1	Jan	2011	Asia	IN	100
2	Feb	2011	EU	UK	150
3	March	2011	US	US	175
4	Jan	2012	Asia	IN	150
5	Feb	2012	EU	UK	200
6	March	2012	US	US	100
7	Jan	2013	Asia	IN	250
8	Feb	2013	EU	UK	300
9	March	2013	EU	UK	350

Listed below are the various aggregate operations which we will be performing using a single Aggregate transformation:

1.) Country-month wise Average units sold:

In this operation, we will perform Group By on Country_Code and Month columns and will take Average of Unit_Sold column.

To perform other aggregate operations we can either use 'Multicast transform' and add new Aggregate transform or we can configure this already used Aggregate Transform to perform all the other operations.

Below mentioned are the steps that need to be followed to use this single Aggregate Transform to perform all operations:

a.) Once we are done with the above stated aggregate operation, click ‘Advanced’ button just above ‘Available Input Columns’. The Aggregate Transform editor window will now look like:

b.) Under ‘Aggregation Name’ type in the name you want to give, in this case I’ll type in ‘Country-month wise Average units sold’. Click on the row below it. The Editor window will now be:

Now we are ready to use this single Aggregate Transform to perform all other aggregate operations we would like to perform on the given dataset.

2.) Country wise total units sold:

For this operation we will perform a Group By on Country_Code column and SUM up Unit_Sold column.

3.) Month wise total units sold:

For this operation we will perform a Group By on Month column and SUM up Unit_Sold column.

4.) Year wise total units sold:

For this operation we will perform a Group By on Year column and SUM up Unit_Sold column.

Once done with all the above operations, click OK.

Connect all these aggregate operations to their respective targets. DFT will now look like:
On successful execution of the DFT, the target files will now hold the required data.

Target files: