Sunday 30 June 2013

SSIS Aggregate Data Flow Transformation

Introduction
As all SQL Developers knows that the Aggregate function performs very important roles in SQL statement. The aggregate function works on Attributes or Columns on a Table Objects. Here in this article we are trying to discuss about the Aggregate Data Flow Transformation control. It works on activity like SUM, AVERAGE, and GROUP BY etc.

Case Study
We have MS SQL Table objects named tbl_STUDTMARKSDTLS it contains the student details and there subject marks details. It also contains the Total marks columns or attributes. That contains the total of each subject marks.

 By using Aggregate Data Flow Transformation control we want to find the Count, Sum, Min, Max of the Total marks and want to store it on a table object named tbl_STUDTMARKS_STATS.

Aggregate Function Details

Aggregate Function
Details
AVERAGE
Give the Average value of the Columns or Attributes value
GROUP BY
Derived the dataset into Group
SUM
Give the SUM value of the Columns or Attributes value
COUNT
Give the number of item in Groups
COUNT DISTINCT
Give the Unique NON NULL Item
MINIMUM
Give the Minimum Number in a Group
MAXIMUM
Give the Maximum Number in a Group


How we configure it

Step-1 [ The Source and Destination Table Objects ]

-- Creating Base table
IF OBJECT_ID(N'tbl_STUDTMARKSDTLS', N'U') IS NOT NULL
   BEGIN
       DROP TABLE tbl_STUDTMARKSDTLS;
   END
GO
CREATE TABLE tbl_STUDTMARKSDTLS
       (STUDID          INT          NOT NULL IDENTITY PRIMARY KEY,
        STUDNAME        VARCHAR(50)  NOT NULL,
        ENGMARKS        DECIMAL(20,2)NOT NULL,
        MATHMARKS       DECIMAL(20,2)NOT NULL,
        BIOLOGYMARKS    DECIMAL(20,2)NOT NULL,
        TOTALMARKS      DECIMAL(20,2)NOT NULL);
GO
-- Inserting Records
INSERT INTO tbl_STUDTMARKSDTLS 
       (STUDNAME, ENGMARKS, MATHMARKS, BIOLOGYMARKS, TOTALMARKS)
VALUES ('Student-A', 70.00, 90.00, 67.00, 227.00),
       ('Student-B', 75.00, 95.00, 62.00, 232.00),               
       ('Student-C', 35.00, 55.00, 22.00, 112.00);
GO      
-- The Student details records
SELECT  STUDNAME, ENGMARKS, MATHMARKS, BIOLOGYMARKS, TOTALMARKS AS TOTAL
FROM    tbl_STUDTMARKSDTLS;

STUDID STUDNAME    ENGMARKS    MATHMARKS    BIOLOGYMARKS              TOTAL
1              Student-A           70.00                     90.00                     67.00                                     227.00
2              Student-B           75.00                     95.00                     62.00                                     232.00
3              Student-C           35.00                     55.00                     22.00                                     112.00

-- Destinaton Table Object
IF OBJECT_ID(N'tbl_STUDTMARKS_STATS', N'U') IS NOT NULL
   BEGIN
     DROP TABLE tbl_STUDTMARKS_STATS;
   END
GO  
CREATE TABLE tbl_STUDTMARKS_STATS
      ( COUNT_TOTALMARKS  DECIMAL(20,2)NOT NULL,
        SUM_TOTALMARKS    DECIMAL(20,2)NOT NULL,
        MIN_TOTALMARKS    DECIMAL(20,2)NOT NULL,
        MAX_TOTALMARKS    DECIMAL(20,2)NOT NULL);
GO         

Step-2 [ SSIS Data Flow Task Details ]



Step-3 [ OLE DB Source Editor Configuration ]



Step-4 [ Aggregate Transformation Editor Configuration ]



Step-5 [ OLE DB Destination Editor Configuration ]





Step-6 [ Run the Package ]



SELECT * FROM tbl_STUDTMARKS_STATS;

COUNT_TOTALMARKS  SUM_TOTALMARKS       MIN_TOTALMARKS        MAX_TOTALMARKS
3.00                                        571.00                                   112.00                                   232.00



Hope you like it.





Posted by: MR. JOYDEEP DAS

1 comment: