SQL Server Integration Services

Example 2: Aggregation

This example reads all data from the Sales.SalesOrderHeader table, calculates aggregate values (sums and an average) by sales person, and writes the aggregate values to a table named Sales Order Aggregate Destination in a SQL Server 2005 database table.

Follow these steps:

1. Create a copy of the package in Example 1: Data Flow as a starting point for this example. Right-click on Example1.dtsx under the SSIS Packages node in Solution Explorer and select Copy from the context menu. Right-click on the SSIS Packages node in Solution Explorer and select Paste from the context menu. Rename the copy to Example2.dtsx—click the Yes button when you are prompted about renaming the package object.

2. Double-click Example2.dtsx to open the Example2 package. Select the Data Flow designer.

3. We can keep the data flow source, but we will need a new data flow destination since the record layout is different and the destination is a database table rather than a flat file. Right-click the Sales Order Destination and select Delete from the context menu. We will create the new destination later in this example. We also no longer need the flat file connection manager created in Example 1: Data Flow;right-click Flat File Connection Manager in the Connection Managers pane and select Delete from the context menu to delete it.

4. Create and configure the data aggregation transformation. Drag the Aggregate item from the Data Flow Transformations section of the Toolbox onto the designer below the Sales ...