Pages

Friday, May 9, 2014

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.

No comments:

Post a Comment