MSBI_SSIS_Aggregate Transfermation

Aggregate Transformation :

Generally "Transformations" are used to transform the data to a desired format as data moves from one step to another step.

Aggregate Transformation is used to perform aggregate functions(Group By, Count, SUM, MAX, MIN and Count Distinct) on source data later pushed to destination.

For example there is a source table Employee and the destination(Output on DW) requirement is Total employees on each department, Total Salary on Department wise, Maximum and Minimum Salaries on each department.

We can write a query in SQL Server as:

SELECT DEPTNO   AS   Department,
       COUNT(*) AS  'No.Of Employees',
       SUM(SAL) AS   Toatal_Salary,
       MAX(SAL) AS   Max_Sal,
       MIN(SAL) AS   Min_Sal
FROM Employee
GROUP BY DEPTNO


We can implement it in a SSIS package as below,

1. Take a Data Flow task to control flow,
2. Edit the Data Flow as drag & drop OLEDB Source, Aggregate Transformation and OLEDB Destination to Data Flow for design,
3. Edit the OLEDB Source, provide the connection and authentication details and select the Employee table. 
4. Make a connection from OLEDB Source to Aggregate Transformation.
5. Edit the aggregate transformation as,
6. Now make a conncetion from Aggregate Transformation to OLEDB Destination,
7. Edit the OLEDB Destination and provide the connection, authentication details ,create a  new table by click on "New.." and rename it (or) select the existed one if the destination has already.
8. Run the package and check the destination data.