Mar 8, 2015

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

No comments:

Post a Comment