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:
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 |
I think SSIS is actually a very good tool to look for some very good and interesting information about solving some crucial IT problems.
ReplyDeleteSSIS Postgresql Read
Mua vé tại đại lý vé máy bay Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ bao nhiêu
ve may bay eva tu my ve vn
chuyến bay từ frankfurt đến hà nội
bay từ nga về việt nam
chuyến bay từ anh về việt nam
bay từ pháp về việt nam mấy tiếng