Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
SSIS

SSIS- Aggregate Transform

 
Updated on Sep 30, 2020

This is a Fast Forward tutorial on Aggregate Transformation in Microsoft SQL Server Integration Services.

Aggregate Transformation

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. It does not support an error output.

Implementation

Here in this example we want to get the sum of the salary for each departments based on the employee data. We want to perform database equivalent of SUM(SAL) GROUP BY DEPTNO operation.

Control Flow
Control Flow

Here, we have EMP table as OLEDB Source. Next we use an Aggregate transform followed by Excel Destination.

Data Flow
Data Flow

Double-click the Aggregate transform to open the editor. Next in the lower pane we select the Input Column, set Output Alias to columns, select the Operation i.e. Group By clause or any aggregate functions as below:

Aggregate Transformation Editor- Aggregations
Aggregate Transformation Editor- Aggregations

Below Aggregate Transformation Editor- Advanced

Aggregate Transformation Editor- Advanced
Aggregate Transformation Editor- Advanced

Let's explore the Advanced Editor properties.

Aggregate Transform Advanced Editor- Component Properties
Aggregate Transform Advanced Editor- Component Properties

Below Aggregate Transform Advanced Editor- Input Columns

Aggregate Transform Advanced Editor- Input Columns
Aggregate Transform Advanced Editor- Input Columns

Below Aggregate Transform Advanced Editor- Input & Output Properties

Aggregate Transform Advanced Editor- Input & Output Properties
Aggregate Transform Advanced Editor- Input & Output Properties

Next we take a look into the Excel Destination object. Double-click to open the editor. Select the connection object from OLEDB Connection Manager browser. Select Data access mode to table or view and set the Name of the Excel sheet.

Excel Destination Editor- Connection Manager
Excel Destination Editor- Connection Manager

Next we go to the Mapping tab and map the Input to Destination columns.

Excel Destination Editor- Mappings
Excel Destination Editor- Mappings

Below Excel Destination Editor- Error Output

Excel Destination Editor- Error Output
Excel Destination Editor- Error Output

Let's take a look into the Advanced Editor of Excel Destination.

Excel Destination Advanced Editor- Connection Managers
Excel Destination Advanced Editor- Connection Managers

Below Excel Destination Advanced Editor- Component Properties

Excel Destination Advanced Editor- Component Properties
Excel Destination Advanced Editor- Component Properties

Below Excel Destination Advanced Editor- Column Mappings

Excel Destination Advanced Editor- Column Mappings
Excel Destination Advanced Editor- Column Mappings

Below Excel Destination Advanced Editor- Input & Output Properties

Excel Destination Advanced Editor- Input & Output Properties
Excel Destination Advanced Editor- Input & Output Properties

Aggregate transform aggregates and groups values in a dataset. This transformation does not pass through any columns, but creates new columns in the data flow for the data it publishes. Only the input columns to which aggregate functions apply or the input columns the transformation uses for grouping are copied to the transformation output.

NULL Handling

The Aggregate transformation handles null values in the same way as any ANSI compliant database.

  • 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 column.
  • In the COUNT (*) function, all rows are counted, including rows with null values.

We will discuss more about the Performance considerations while working with Aggregate transformation in another article. We will discuss on the properties like- Comparison Flags, Count Distinct Scale, Count Distinct Keys, Keys scale, Number of keys, Count distinct scale, Count distinct keys, Auto extend factor etc. Stay tuned...

PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles