O'Reilly logo

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook by Pedro Perfeito, Reza Rad

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required