MSBI # 57 – SSIS # 22 – Data Flow Task # 8 – Aggregate Transformation Task – Detailed information & Explanation with example , Use & Properties

Hi folks

In my all previous post for SSIS I have cover almost all introductory part for all  Data Flow Task ,from now onwards we are going to cover Detailed information & Explanation with example , Use & Properties for each task individually.

My last post link so not to break continuity for reader who have missed ..

• MSBI # 55 – SSIS # 21 – Data Flow Task # 7 – All Transformation in Data Flow task (Introduction and Usage) {Part – VI}

In this article we are going to cover Aggregate Transformation Task with following points

• Why Aggregate Transformation Task is Use ?
• How Aggregate Transformation Task is look like ?
• Features of Aggregate Transformation Task
• How to use Aggregate Transformation Task ?
• What is alternative For Each Aggregate Transformation Task ?
• Demonstration of using Aggregate Transformation Task ?
• Various Properties For each Aggregate Transformation Task ?
• Reference link For Aggregate Transformation Task

Why Aggregate Transformation Task is Use?

This is very much similar to aggregate function of SQL Server where we can perform Group by, Sum, Average, Count, Count distinct, Minimum, Maximum

At the transformation level, We can configure the Aggregate transformation for performance by specifying the following values:

• The number of groups that are expected to result from a Group by operation
• The number of distinct values that are expected to result from a Count distinct operation.
• The percentage by which memory can be extended during the aggregation

How Aggregate Transformation Task is look like?

Features of Aggregate Transformation Task

The Aggregate transformation supports the following operations

• Group by
• Sum
• Average
• Count
• Count distinct
• Minimum
• Maximum

Some more key features

• We must Required input column to perform any operation in this task 
• In the aggregate task we are having options for key count, key scale, distinct key count, and distinct key scale apply at the component level when specified on the Advanced tab, at the output level when specified in the advanced display of the Aggregations tab, and at the column level when specified in the column list at the bottom of the Aggregations tab.
• In the Aggregate transformation, Keys and Keys scale refer to the number of groups that are expected to result from a Group by operation.
• Count distinct keys and Count distinct scale refer to the number of distinct values that are expected to result from a Distinct count operation.

How to use Aggregate Transformation Task?

We can configure the Aggregate transformation for performance by specifying these values:

• The number of groups that are expected to result from a Group by operation on the column.

• The number of distinct values that are expected to result from a Count distinct operation on the column.

  The Aggregate transformation supports the following operations.

Where

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

What is alternative For Each Aggregate Transformation Task?

We can use Execute SQL task for sure to perform aggregate operations on Data or We can have TSQL in OLE DB Command Task.

Demonstration of using Aggregate Transformation Task?

Select Aggregate Transformation for Data flow task tab

As I have already said aggregate task must required input otherwise it will trough following errors

When we select input values and open aggregate transformation editor it shows following various option

Lets add Data viewer and see the output

After running we we have following result

In give Result all fields are Aggregate result fields as configured in editor and as shown

Various Properties for each Aggregate Transformation Task?

Following are properties in Aggregate transformation Task

Reference link for Aggregate Transformation Task

• Aggregate Transformation
• Aggregate Transformation Editor (Aggregations Tab)
• Aggregate Transformation Editor (Advanced Tab)
• SSIS New Features in SQL Server 2008 – Part 4 – SQL Server Performance
• SQL Server SSIS Aggregate Transformation | BI Monkey

For More explanation  with Example

• SQL Troubles: SSIS: Aggregate Data Flow Transformation
• http://www.c-sharpcorner.com/uploadfile/ae35ca/sql-server-integration-services-ssis-aggregate-group-by-transformations-control-in-ssis/

Hope this helps !!

Hope you have understood Aggregate Transformation Task in SSIS and ready to use every aspects for same

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource Website>> Dactylonomy of Web Resource