Aggregate
transformation or component can have only input but more than one \multiple
output. It means there is no need to create multiple data source or
multi cast transformation. In this example we are going to illustrate how
to configure two output pipelines in aggregate transformation. 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:
Query 1:
SELECT
ntUserID,
COUNT(*) AS ntTotalCount,
SUM(ntMarks) AS ntTotalMarks
FROM
tblUserMarks
GROUP BY ntUserID
Query 2:
SELECT
vcSubjectName,
AVG(ntMarks) AS ntAvgMarks
FROM tblUserMarks
GROUP BY vcSubjectName
Step 1: Add any source inside data
flow task. In this example our data source is SQL Server. Connect source transformation
to database and choose your source table.
Step 2: Drag and drop Aggregate
transformation from toolbox.
Step 4: Double click on Aggregate transformation. It
will pop up aggregate transformation editor.
Step 5: Configured the aggregate transformation
editor for query 1.
 |
| Multiple output Aggregate configuring first query |
Step 6: To configure the aggregate
for query 2 we will have to click on advanced button which is inside the red rectangle
in the above diagram.
 |
| Multiple output Aggregate Advance Section |
Step 7: Here Aggregate output 1 is
for query 1. In the same we are going to configure Aggregate Output 2 for query
2:
 |
| Multiple output Aggregate configuration second |
Step 8: Now creating two sql server
destinations and executing the package!!
 |
| Multiple output Aggregate Running |
No comments:
Post a Comment