Aggregate Transformation in SSIS

The Aggregate transformation applies aggregate functions, such as Average, to column values and copies the results to the transformation output. Besides aggregate functions, the transformation provides the GROUP BY clause, which you can use to specify groups to aggregate across.

With the help of this Transformation we can perform following operations.

Ø  Group by

Ø  Sum

Ø  Average

Ø  Count

Ø  Count distant

Ø  Minimum

Ø  Maximum

The Aggregate transformation handles null values in the same way as the SQL Server relational database engine.

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

Step by step example.

In control flow you take Data flow task.

Double click on the Data flow task. You will get data flow window. Here I am taking data flow source as OLEDB source and make connection to the data base.

Take data flow transformation

Double click on the Aggregate Transformation

Select the operation.

Here I am performing the sum operation.

Select the data flow destination

I am taking the data flow destination as flat file destination. Double click on that you will get below screen

Click on new

Select delimited and click ok.

Select the location where you want to store the flat file.

Now execute the package.

Package executed successfully.