SSIS - How To Use Aggregate Transformation (GroupBy, Sum) in SSIS Package

Scenario:

Let’s say we received a file from our client with columns (CountryName, SalePersonName, SaleAmount). We need to load a table with summary data having only two columns CountryName, TotalSaleAmount)

Solution:

 In this case we cannot run TSQL query on Source as our source is flat file. We are going to use Aggregate Transformation to achieve this in SQL Server Integration Services.

Step1:

Create connection to Flat file by using Flat File Connection As shown. Here is sample data that I used in source flat file

CountryName,SalePersonName,SaleAmount
USA,Jack,100
USA,Andy,200
USA,Mike,500
Brazil,Sara,1000
Brazil,Barbra,200

Use Flat file Source to read data from Flat file in SSIS Package 

Step 2:

Bring Aggregate transformation to Data Flow Pane and then connect with Flat File Source. Select Columns and Operation as shown below to get TotalSaleAmount by CountryName. We selected Sum as we want to find out the TotalSaleAmount by CountryName.

How to use Aggregate Transformation in SSIS Package with Group By and Sum Operation


Following operations are available by using Aggregate Transformation
  • Group By 
  • Count
  • Count Distinct
  • Sum
  • Average 
  • Minimum
  • Maximum
Note:
 The operation will appear in drop down depending upon the data type of field. If your input column is string type , you will not see Sum, Count and other aggregate functions but only Group by. Always make sure to convert data type of column to proper data type before providing as input to Aggregate Transformation.



Instead of using Table as destination, I used Multicast for testing purpose and used Data Viewer to show output data produced by Aggregate Transformation. After testing our package successfully we can delete Multicast and Data Viewer and bring our destination and connect to Aggregate Transformation for input columns for our destination.

How to use Aggregate Transformation in SSIS Package to get Sum with Group By 





2 comments: