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