Sunday 30 June 2013

SSIS Lookup Transform

Introduction

SSIS data flow control has a very important transform called the Lookup Transform. So I decide to write this article related to it.


What is the use of Lookup Transform
The main functionality of the lookup join is to make a Join with other source with current source and fetch the result in a desired format.

[Current Source] + [Other Source] à [Fetch Result] à[Desired Format]


The source can be anyone of the following Chased object, Table, Destination file source, a result from query etc.
The Lookup transform be available for data source like SQL, ORACLE and DB2.


Case Study
Here we have a flat file (FlatFile-A.txt) which contains the product information. There is a SQL table object named tbl_LOOKUPMATCH which contain the match records of product information. The SSIS package use the Lookup Transform and segregate the matched records from flat file to a table objects called tbl_LOOKUPMATCHDESTINATION and unmatched records to another table objects called tbl_LOOKUPUNMATCHDESTINATION.
Please follow the diagram to understand the case study.



Configuring the Lookup Transform

Step-1 [ Flat file source, Lookup Match and Destination Table Objects ]



Creating Lookup Match and Destination Table Objects

-- Lookup Match Table
IF OBJECT_ID(N'tbl_LOOKUPMATCH', N'U') IS NOT NULL
   BEGIN
      DROP TABLE tbl_LOOKUPMATCH;
   END
GO

CREATE TABLE  tbl_LOOKUPMATCH
       (
          PRODCD   VARCHAR(50) NOT NULL PRIMARY KEY,
          PRODNAME VARCHAR(50) NOT NULL
       );
GO
-- Inserting Records in Lookup Match Table
INSERT INTO  tbl_LOOKUPMATCH 
       (PRODCD, PRODNAME)
VALUES ('1', 'RICE'),
       ('2', 'OIL');   


Lookup Match tables Records

-- Records of Lookup match table      
SELECT * FROM tbl_LOOKUPMATCH; 


PRODCD               PRODNAME
1                              RICE
2                              OIL

-- Creating Match Lookup Destination
IF OBJECT_ID(N'tbl_LOOKUPMATCHDESTINATION', N'U') IS NOT NULL
   BEGIN
      DROP TABLE tbl_LOOKUPMATCHDESTINATION;
   END
GO
CREATE TABLE  tbl_LOOKUPMATCHDESTINATION
       (
          PRODCD   VARCHAR(50) NOT NULL PRIMARY KEY,
          PRODNAME VARCHAR(50) NOT NULL
       );
GO 

-- Creating Un Match Lookup Destination
IF OBJECT_ID(N'tbl_LOOKUPUNMATCHDESTINATION', N'U') IS NOT NULL
   BEGIN
      DROP TABLE tbl_LOOKUPUNMATCHDESTINATION;
   END
GO
CREATE TABLE  tbl_LOOKUPUNMATCHDESTINATION
       (
          PRODCD   VARCHAR(50) NOT NULL PRIMARY KEY,
          PRODNAME VARCHAR(50) NOT NULL
       );
GO           

Step-2 [ SSIS Data flow ]



Step-3 [ Configuring Flat file source Editor ]



Step-4 [ Configuring Lookup Transform Editor ]







Step-5 [ Configuring OLE DB Destination Editor ]



Step-6 [ Run the SSIS Package ]






Hope you like it.




Posted by : MR. JOYDEEP DAS

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

SSIS Derived Columns Transformation

Introduction
The derived columns Transformation is used in the situation where we want some manipulation with existing columns data and store it in a separate column.

Case Study
We have table objects called tbl_STUDENTREC where we have Student Roll, Student First Name, Student Middle Name, Student Last Name, Marks1, Marks2, Marks3 etc. Now we have a destination table object called tbl_STUDENTDTLS which contains Student Roll, Student Name, Total Marks. So we have to make some manipulation with first table columns to get the desire result for second table.

For tbl_STUDENTDTLS

[tbl_STUDENTDTLS].[Student Name]  = [tbl_STUDENTREC].[Student First Name]  +
                      [tbl_STUDENTREC]. [Student Middle Name] + 
                      [tbl_STUDENTREC].[Student Last Name]

[tbl_STUDENTDTLS].[ Total Marks]     = [tbl_STUDENTREC].[Marks1]  +
                       [tbl_STUDENTREC]. [Marks2] + 
                       [tbl_STUDENTREC].[Marks3]

How to Configure the SSIS Package

Step-1 [ Create Source and Destination Table Objects ]

/*
We have table objects called tbl_STUDENTREC where we have Student Roll name, Student First Name,
Student Middle Name, Student Last Name, Marks1, Marks2, Marks3 etc. Now we have a destination table
object called tbl_STUDENTDTLS which contains Student Roll, Student Name, Total Marks. So we have to make
some manipulation with first table columns to get the desire result for second table.
*/

-- Creating Source Table Objects
IF OBJECT_ID(N'tbl_STUDENTREC', N'U') IS NOT NULL
   BEGIN
      DROP TABLE tbl_STUDENTREC;
   END
GO 
CREATE TABLE tbl_STUDENTREC
       (STUDROLL        INT         NOT NULL PRIMARY KEY,
        STUDFIRSTNAME   VARCHAR(50) NOT NULL,
        STUDMIDNAME     VARCHAR(50) NULL,
        STUDLASTNAME    VARCHAR(50) NOT NULL,
        MARKS1          DECIMAL(20,0) NOT NULL,
        MARKS2          DECIMAL(20,0) NOT NULL,
        MARKS3          DECIMAL(20,0) NOT NULL);
GO
-- Inserting Records in Source Table
INSERT INTO tbl_STUDENTREC            
   (STUDROLL, STUDFIRSTNAME, STUDMIDNAME, STUDLASTNAME,
    MARKS1, MARKS2, MARKS3)
VALUES (1, 'Subal', 'Chandra', 'Das', 90.00, 67.00, 87.00),
       (2, 'Subash', 'Chandra', 'Das', 87.00, 69.00, 37.00),   
       (3, 'Rajarshi', '', 'Roy Chowdhury', 90.00, 97.00, 67.00), 
       (4, 'Sarmila', 'Das', 'Chowdhury', 45.00, 47.00, 77.00),
       (5, 'Kajal', '', 'Gupta', 20.00, 37.00, 47.00);                 

-- Disply Records From Source Table
SELECT STUDROLL, STUDFIRSTNAME, STUDMIDNAME, 
       STUDLASTNAME, MARKS1, MARKS2, MARKS3
FROM   tbl_STUDENTREC;

STUDROLL STUDFIRSTNAME STUDMIDNAME STUDLASTNAME   MARKS1 MARKS2 MARKS3
1        Subal         Chandra      Das           90     67     87
2        Subash        Chandra      Das           87     69     37
3        Rajarshi                   Roy Chowdhury 90     97     67
4        Sarmila       Das          Chowdhury     45     47     77
5        Kajal                      Gupta         20     37     47


-- Creating Destination Table Objects
IF OBJECT_ID(N'tbl_STUDENTDTLS', N'U') IS NOT NULL
   BEGIN
      DROP TABLE tbl_STUDENTDTLS;
   END           
GO
CREATE TABLE tbl_STUDENTDTLS
       (STUDROLL        INT             NOT NULL PRIMARY KEY,
        STUDFULLNAME    VARCHAR(150)    NOT NULL,
        STUDTOTALMARKS  DECIMAL(20,0)   NOT NULL);
GO

          
Step-2 [ Data Flow ]



Step-3 [ OLE DB Source Editor Configuration ]





Step-4 [ Derived Columns Transform Editor Configuration ]



Step-5 [ Data Conversion Transform Editor Configuration ]



Step-6 [ OLE DB Destination Editor Configuration ]





Step-7 [ Run the Package ]




Step-8 [ Analyze the Output of SSIS Package ]






Hope you like it.





Posted by: MR. JOYDEEP DAS