Hands-On Microsoft SQL Server 2008 Integration Services part 46. Deploy and manage high-performance data transformation solutions across your enterprise using the step-by-step techniques in this fully revised guide. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition explains the tools and methods necessary to extract conclusive business intelligence from disparate corporate data. Learn how to build and secure packages, load and cleanse data, establish workflow, and optimize performance. Real-world examples, detailed illustrations, and hands-on exercises are included throughout this practical resource. . | 428 Hands-On Microsoft SQL Server 2008 Integration Services Figure 10-13 Configurations for the Unpivot transformation Chapter 10 Data Flow Transformations 429 Aggregate Transformation The Aggregate transformation is an asynchronous transformation that helps you to perform aggregate operations such as SUM AVERAGE and COUNT. To perform these aggregate operations you need to have a complete data set. The Aggregate transformation consumes all the rows before applying any aggregation and extracting the transformed data. Because of being an asynchronous transformation the output data which most likely has a new schema is populated in the new memory buffers. The Aggregate transformation can perform operations such as AVERAGE COUNT COUNT DISTINCT GROUP BY selecting a minimum or maximum from a group and SUM on column values. The aggregated data is then extracted out in the new output columns. The output columns may also contain the input columns which form part of the groupings or aggregations. When you select a column in the Aggregate Transformation Editor and click in the Operation field you will see a list of operations that coincide with the type of the column you selected. This makes sense as the aggregate operations require appropriate column types for example a SUM would work on a numeric data type column and would not work with a string data type column. Following are the operation descriptions in detail AVERAGE This operation is available only for numeric data type columns and returns the average of the column values. COUNT Counts the number of rows for the selected column. This operation will not count the rows that have null values for the specified column. In the Aggregate Transform Editor a special column has been added that allows you to perform the COUNT ALL operation to count all the rows in a data set including those with null values. COUNT DISTINCT Counts the number of rows containing distinct non-null values in a group. GROUP BY This operation can be .
