Aggregate Transformation in SSIS Basic Mode

The Aggregate Transformation in SSIS performs two main tasks:

  1. Applying Aggregate Operations on your input data
  2. It allows you to group your data based on values in your input columns.
Aggregate Transformation in SSIS 2014 Basic Mode 0

Aggregate Transformation in SSIS performs a function similar to the SQL Agg functions. The operations include the following:

  1. GROUP BY: Just like the GROUP BY Clause in SQL SELECT queries.
  2. COUNT: It will Count the number of values in this column. Null values are included in the count if you select (*) as the input column. Otherwise, null values are ignored.
  3. COUNT DISTINCT: It will count the number of Distinct values in this column.
  4. SUM: Calculate the Sum of the Column values.
  5. AVERAGE: Calculate the Average of the Column values.
  6. MINIMUM: Calculate the Minimum of Column values.
  7. MAXIMUM: Calculate the Maximum of Column values.

The Aggregate transformation in SSIS can configure in Basic mode or Advanced mode. In Basic mode, the Aggregate transformation has one output. And in Advanced mode, it can have more than one output, each containing the result of a different aggregation.

Let us see how to configure the Aggregate transformation in SSIS to produce a single output with an example.

TIP: Please refer to Transformation in Advanced Mode article to know, How to Configure multiple outputs in Aggregate Transformation

IsBig Property

The Aggregate transformation in SSIS includes the IsBig property, which is used on the output columns to handle big or high-precision numbers. If any of the column values may exceed 4 billion, then IsBig should be set to 1. By setting the IsBig property to 1, Aggregation Transformation Will:

  1. Use the DT_R8 data type instead of the DT_R4 data type.
  2. Use the DT_UI8 data type to store the Count results.
  3. You can use the DT_UI4 data type to store the Distinct count results.

Aggregate Transformation in SSIS Basic Mode Example

For instance, If we want to find the sum of the sales amount for each Color, then we can use this SSIS Aggregate Transformation. In this example, we are going to perform all the Aggregate Transformation operations on DimGeography and FactResellerSales tables present in AdventureworkDW2014.

STEP 1: Open BIDS and Drag and drop the data flow task from the toolbox to control flow and name it Aggregate Transformation.

Aggregate Transformation in SSIS 2014 Basic Mode 1

Double-click on it will open the data flow tab.

STEP 2: Drag and drop OLE DB Source, Aggregate Transformation from a toolbox to data flow region

Aggregate Transformation in SSIS 2014 Basic Mode 2

STEP 3: Double click on the OLE DB source in the data flow region will open the connection manager settings and provides space to write our statement. Here we are using DimGeography and [FactResellerSales] present in the AdventureworkDW2014

The command we are using to retrieve data for SSIS aggregate transformation basic mode is:

USE [AdventureWorksDW2014] 
GO 
SELECT [EnglishCountryRegionName]
      ,[StateProvinceName]
      ,[City]
      ,[PostalCode]
      ,RESELLER.[UnitPrice]
      ,RESELLER.[ProductStandardCost]
      ,RESELLER.[TotalProductCost]
      ,RESELLER.[SalesAmount]
      ,RESELLER.[TaxAmt]
    
FROM [DimGeography]
 INNER JOIN 
     [FactResellerSales] AS RESELLER ON
 [DimGeography].[SalesTerritoryKey] = RESELLER.[SalesTerritoryKey]
Aggregate Transformation in SSIS 2014 Basic Mode 3

STEP 4: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

Aggregate Transformation in SSIS 2014 Basic Mode 4

Drag and drop the blue arrow from OLE DB Source to Aggregate Transformation to connect the data.

SSIS Aggregate Transformation Editor

STEP 5: Double-click the SSIS Aggregate Transformation to open the editor. Next, in the lower pane, we select the required columns to perform Aggregate Transformation operations by checking them in Available Input Columns.

Once you check the required columns, then those column names will appear in the Input Column located in the lower pane.

Aggregate Transformation in SSIS 2014 Basic Mode 5
  • Input Columns: Column we received from the OLE DB source.
  • Output Alias: This is the same as the Alias column. Here we can change the column name as per the project requirements.
  • Operations: We already discussed this above.

For this SSIS Aggregate Transformation example, we are going to use most of the Aggregate functions below

Aggregate Transformation in SSIS Basic Mode 6

From the above SSIS aggregate transformation screenshot, you can observe that we measure the Sum of Unit Price, Product Standard Cost, Minimum Total Product Cost, Maximum Sales Amount, and Average Tax Amount. And Group by English Country Region, Then by State Province Name, and Then by City.

NOTE: If your input column is a string type, you will not see Sum, Average, and other aggregate functions but only Group by. Always make sure to convert the data type of the column to the proper data type before providing input to Aggregate Transformation.

Click ok.

STEP 6: Drag and drop the OLE DB Destination into the data flow region and drag the blue arrow from Aggregate Transformation to OLE DB Destination

Aggregate Transformation in SSIS Basic Mode 7

STEP 7: Now, we have to provide the Server, database, and table details of the destination. So double-click on the OLE DB Destination and provide the required information.

Aggregate Transformation in SSIS Basic Mode 8

Here we pointed to our destination database. For the time being, I used the New button to create a destination table for me and named it Aggregate Transformation Basic Mode. But in reality, you should not design your table like this.

STEP 8: Click on the SSIS Aggregate Transformation Mappings tab to check whether the source columns are exactly mapped to the destination columns.

Aggregate Transformation in SSIS Basic Mode 9

Click ok to finish our Aggregate Transformation in SSIS package design. Let us run the package

Aggregate Transformation in SSIS 2014 Basic Mode 10

Let’s open the SQL Server Management Studio and write the below statement to check the results

SELECT [EnglishCountryRegionName]
      ,[StateProvinceName]
      ,[City]
      ,[UnitPrice]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
  
FROM [Aggrigate Transformation Basic Mode]
Destination Table

Aggregate Transformation Advanced Option

The Aggregate Transformation in SSIS includes a set of properties in the Advanced tab present in the Editor. By setting these properties, we can increase the performance of the Aggregate Transformation.

Aggregate Transformation in Basic Mode 12

Key Scale: In this option, specify the approximate number of keys an aggregation can write. By default, the value of this option is Unspecified.

ValueDescription
UnspecifiedThe Key Scale property is not used.
LowAggregation can write nearly 500,000 keys.
MediumAggregation can write nearly 5,000,000 keys.
HighAggregation may write more than 25,000,000 keys.
  • Keys: Specifying the exact number of keys an aggregation can write. Keys refers to the number of groups that are expected to result from a Group by the operation. If both the Key Scale and Keys properties are set, the value of Keys takes precedence.
  • Count Distinct Scale: We can specify the approximate number of distinct values an aggregation can write.
  • Count Distinct Keys: In this, We can specify the exact number of distinct values that the aggregation can write. If both CountDistinctScale and CountDistinctKeys are specified, CountDistinctKeys takes priority.
  • Auto extend factor: In this SSIS Aggregate Transformation option, We can specify the percentage of memory it can extend in the aggregation process. We can choose the values from 1 to 100. By default, the value of this option is 25%.

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.