Aggregate Transform: aggregating the data stream

Aggregate Transform provides a way to group data by column(s) and apply aggregation functions on them.

In this recipe we will fetch each Sales Person according to the start and end date of sales and the sum of their sales quota by resorting to an Aggregate Transform.

How to do it...

1. Create a new Integration Services project and name it R03_Aggregate.

2. Add a Data Flow Task and in the Data Flow tab add an OLE DB Source and then create a connection to the AdventureWorks2012 sample database. Set the data access mode to SQL Command and enter the following T-SQL code in the SQL Command field:

   SELECT SLHST.QuotaDate, SLHST.SalesQuota, 
     PER.FirstName, PER.LastName 
   FROM Sales.SalesPersonQuotaHistory SLHST 
    INNER JOIN Person.Person PER 
    ON SLHST.BusinessEntityID = PER.BusinessEntityID

3. Click on Preview and check the structure of your data.

   

4. Drag-and-drop an Aggregate Transform right after the OLE DB Source and connect the Data Path to the aggregate transformation...