Using aggregate functions, you can find sum, maximum, minimum, average
values of items in a table or a group.
The aggregate functions Count, Sum, Average, Min, and Max are all defined
according ANSI SQL-92 standard. This standard also describes how nulls are
handled while aggregating. The Aggregate Data Flow Transformation does the
aggregation of data in the context of SSIS.
Using an Aggregate Data Transformation
[ 148 ]
Hands-On Exercise: Using Aggregate
Data Flow Transformation
In order to follow the steps as indicated, you will need a data flow task that connects
to a data source and a Recordset Destination to which the data can flow. You will
introduce an aggregate data flow component and a percentage sampling data flow
item in the path of the data.
In this exercise, you will be aggregating data from the OrderDetails table discussed
in Chapter 6. The next screenshot shows how an aggregate query is posed and the
result of running this query in SQL Server 2005's Management Studio. The aggregate
values (SUM UnitPrice and QuantityMax) are grouped by OrderID.
Chapter 9
[ 149 ]
The following are the major steps in this exercise:
Create a BI Project and add a Data Flow Task. Add and configure the
DataReader Source to extract data from the Local SQL Server.
Add an Aggregate Data Transformation
Establish a path to connect DataReader Source with the Aggregate Data
Transformation.
Configure the Aggregate Data Flow Transformation.
Pages:
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115