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

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?

image_thumb

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.

image5

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

image

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

image

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

image

Lets add Data viewer and see the output

image

After running we we have following result

image

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

image

Various Properties for each Aggregate Transformation Task?

Following are properties in Aggregate transformation Task

image

Reference link for Aggregate Transformation Task

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

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: