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
