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