Mar 8, 2015

Multiple output of aggregate transformation configuration SSIS

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