AGGREGATE TRANSFORMATION

Introduction:

 Using aggregate transformation we can perform basic aggregate functions, such as Count, Count distinct, Sum, Average, Max and Min. It sends the results to the transformation output. The transformation provides the Group by clause to specify the groups for aggregation.

Requirement:

Source table:

            My source table is “Daily_sales” which is having two days (25^th and 26^th) sales information shown below screen shot.

 

Target table:

            Target table (Total_sales) is having two fields "Sell_date and Total_price". We need to find day wise total sales and load it into target table.

Expected result:

Package creation:

• In control flow items, take Data flow task. In data flow items, take “OLEDB source” and create mappings to source table (Daily_salues).
• Drag and drop “Aggregate” transformation into designer surface.
• Connect source green arrow to the “Aggregate” Transformation. Edit the “Aggregate” transformation, then “Aggregate transformation editor” window will open, shown below.

• Choose required fields by selecting the check boxes, shown below.

• In “Aggregate transformation editor”, bottom side we can find the “Input column, output alias, Operation etc”. In the operation section, we should mention sum operation for “Price” and group by operation for “Sell_date”. In "Output Alias" section change field name price to "Total_price".

• Click on “OK” button to finish aggregations.
• Drag and drop “OLEDB destination” into designer surface map to the target table (Total_sales) and execute the package.

Actual result from target table: