SSIS Aggregate very simple example | Demo

SSIS Aggregate Transformation | Component by simple example for beginner step by Step in BIDS

Use: Aggregate Transformation is used to get the aggregated value of given input. Let's assume a table named tblUserMarks with below data:

UserMarksID	ntUserID	vcSubjectName	ntMarks
1	1	SQL Server	30
2	1	SSRS	20
3	1	SSIS	25
4	1	SSAS	15
5	2	SQL Server	32
6	2	SSRS	23
7	2	SSIS	28
8	2	SSAS	16

We to execute below query using Aggregate Transformation:

SELECT

     ntUserID,

     COUNT(*) AS ntTotalCount,

     SUM(ntMarks) AS ntTotalMarks

FROM tblUserMarks

GROUP BY ntUserID

Step 1: Add any source and destination inside data flow task. In this example my source and destination both are SQL Server.

Step 2: Drag and drop Aggregate transformation from toolbox as shown in below diagram:

Aggregate Transformation Simple example

Step 3: Connect source transformation to database and choose your source table.

Step 4:  Double click on Aggregate transformation. It will pop up aggregate transformation editor:

a. Available input column: Choose ntUserID, ntMarksID and (*)

b. Input column: All selected available input column will automatically appear here.

c. Output Alias: Here we can rename the output columns.

d. Operation:  We must have to choose an operation for each input columns from drop down. There must be at least one group by operation on any column according to our requirement.   

Aggregate SSIS Group By

Step 5: Connect destination transformation with database and mapped the columns on destination tables.

Step 6: Now execute the package!!!

BIDS SSIS Run Aggregate Beginners

Output:

ntUserID	ntTotalCount	ntTotalMarks
1	4	90
2	4	99