SSIS - How does Aggregate Transformation work

In this tutorial, we are going to learn how does Aggregate Transformation work in SSIS package. The Aggregate transformation is used to perform aggregate operations/functions on groups in a dataset. The aggregate functions available are- Count, Count Distinct, Sum, Average, Minimum and Maximum. The Aggregate transformation has one input and one or more outputs.

The Aggregate transformation handles null values in the same way as the SQL Server relational database engine. The behaviour is defined in the SQL-92 standard. The following rules apply:

• In a GROUP BY clause, nulls are treated like other column values. If the grouping column contains more than one null value, the null values are put into a single group.
• In the COUNT (column name) and COUNT (DISTINCT column name) functions, nulls are ignored and the result excludes rows that contain null values in the named column.
• In the COUNT (*) function, all rows are counted, including rows with null values.

Aggregate Transformation Configuration - You configure the Aggregate transformation at the transformation, output, and column levels. At the transformation level, you configure the Aggregate transformation for performance by specifying the following values:

1. The number of groups that are expected to result from a Group by operation. They are mostly are string values.
2. The number of distinct values that are expected to result from a Count distinct operation. These values could be string or numeric.
3. The percentage by which memory can be extended during the aggregation.
4. The Aggregate transformation can also be configured to generate a warning instead of failing when the value of a divisor is zero.

For more on Aggregate Transformation - Watch a Live demo here


Conclusion - The Aggregate transformation is asynchronous, which means that it does not consume and publish data row by row. Instead it consumes the whole rowset, performs its groupings and aggregations, and then publishes the results.