- It is same as GROUP BY in SQL
- Using this Transformation, we can get COUNT(*), COUNT DISTINCT, MAX, MIN, AVE, SUM
- Column can be part of GROUP BY / AGGREGATION Function
- It distributes input into more than one output
- Unlike Conditional Split, it is not splitting the output, instead it is sending the all the records to all output branches
Following script has to be executed
CREATE TABLE AggregateDemo (ADID INT IDENTITY(1,1), StudentID INT, Subject varchar(10), Mark int)
insert into AggregateDemo (StudentID,Subject,Mark)
CREATE TABLE AggregateDemo_TotalMarkPerStudent (StudentID INT, TotalMark int)
CREATE TABLE AggregateDemo_MaxMarkPerSubject (Subject varchar(10), MaxMark int)
1. Add Data Flow Task in Control Flow
2. Drag source in DFT and it should point to AggregateDemo
3. Add Multicast Transformation and connect it with source
4. Add 2 Aggregate Transformations and connect them with Multicast Transformation
5. In 1st Aggregate, do following settings.
6. In 2nd Aggregate, do following settings.
7. Add 2 destinations and connect them with 2 aggregate transformations. These 2 dimensions should point to AggregateDemo_TotalMarkPerStudent & AggregateDemo_MaximumMarkPerSubject respectively.
8. Overall package should look like this.
9. Execute the package. It will look like this.