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
Note: We have video version of this tutorial. Please click the button below otherwise scroll down to read text based tutorial.
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
Contents
-
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
Katie
