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