In this step by step SSIS Tutorial I will show you how to use Aggregate Transformation. For the purpose of this tutorial I will use SSIS 2012
The aggregate transformation allow us to aggregate input rows and works the same as SQL GROUP BY with aggregation functions.
Other Versions of SSIS:
- SSIS 2012
- This blog post has been written using SSIS 2012
- SSIS 2008
- The examples we used should work in SSIS 2008
- SSIS 2005
- We suspect that this will work in SSIS 2005 as well
- SSIS Aggregate Transformation
- Aggregate Transformation Basics
Aggregate Transformation Basics
In my first example I will show you how to aggregate the data and show unique customers with the total sales amount.
NOTE: SQL is below:
Select dc.CustomerAlternateKey, dc.FirstName, dc.LastName, fs.SalesAmount
From DimCustomer As dc INNER Join FactInternetSales As fs
On dc.CustomerKey = fs.CustomerKey
Where (dc.CustomerAlternateKey IN ('AW00021700','AW00021701'))
I have got an open package in Visual Studio In a data flow I have got OLE DB Source and AdventureWorksDW2012 in a connection Menager.
I open OLE DB Source Editor showing the SQL code. I open OLE DB Source Editor showing the SQL code. I click Preview.
I have limited the data to 2 Customers only. We can see that I have got CustomerAlternateKey with FirstName, LastName and SalesAmount. So we can see that the first customer vote something twice and the second Customer vote somethin four times. So I will aggregate the data and show unique customers with the total sales amount so my output should be two rows and aggregated sales amount using SUM() function. And I will do that using Aggregate transformation.
I drag the aggregate from toolbox I will create path between them and I will double click the Aggregate.
I have got four columns but I will only use three. I select FirstName, LastName the data is populated at the bottom. The operation for them will be Group BY. I select one more fild SalesAmout and in OutputAlias I type TotalSalesAmount the defult operation is Sum.
To preview the data I will use derived column. I create new data path and Unable data viewer.
I run the package and I get two clients with total sales amount.
So that is on the very basic level aggregate function.
I hope that will help