About Aggregate Transformation:The Aggregate Transformation provides a means of carrying out some simple aggregations on data selected through source.Available aggregation functions in this transformation are:
- Group By
- Count distinct
Improving Performance in the Aggregate Transformation
The Aggregate Transformation is quickly runs in memory, but if you are shifting very large volumes of data through it and it is slowing down there and results few amount of data. First is the Keys and KeyScale properties. These tell the component how many “Group By” distinct groups it should be prepared to handle. By default the value for KeyScale is “Unspecified”, but can be set to low,medium r high (25m keys). If you want to use more than this you can use the Keys property, which overrides KeyScale, and you can enter the amount of expected Keys. If you are using a CountDistinct aggregation you can set the CountDistinctScale and CountDistinctKeys properties which operate in the same way. Usually there is no need to adjust these properties.
When would you use the Aggregate Transformation?
In my Idea, we should use the Aggregate transformation only when the data source cannot efficiently support the Aggregation processes by itself. If you are reading a data from a relational data source,usually it will have the server to aggregate the data in a query before passing it into SSIS. U may need this when you are hitting a live system and cannot allowed us to load the server with queries, usually when you were reading from a Flat File source then you would have to use the Aggregate Transformation because the File System doesn’t provide any means to perform data operations.