Introduction
Here
in this article we are going to discuss about dynamic Lookup Transform, which
also means the parameterized Lookup Transform. As a SSIS developer we all are
aware about Lookup Transform and how useful it is.
The
Lookup Transform Cache plays a very important role in terms to holding the
record set of Lookup.
There
are three type of Cache mode found in Lookup Transform
1. Full
Cache
2. Partial
Cache
3. No
Cache
If
the data source for Lookup is huge nothing work perfectly in terms of
performance of SSIS package is concern and sometimes the package fails.
So
we have to limit the number of records for Lookup Transform according to our
needs and we want to do it dynamically.
To
understand it properly here we are taking a simple case scenario.
Case Scenario
We
have a simple SSIS package which contains a single Data Flow task. The data flow task retrieve data from a Flat File
and store it in a Table Object.
Sample Flat file: EmployeeDetils.txt
EmployeeID;EmployeeName
101;Joydeep Das
102;Sukamal Jana
103;Deepasree Das
104;Deblina Bhattacharya
105;Priya Bannerjee
106;Shipra Roy Chowdhury
…..
101;Joydeep Das
102;Sukamal Jana
103;Deepasree Das
104;Deblina Bhattacharya
105;Priya Bannerjee
106;Shipra Roy Chowdhury
…..
Note:
Think this flat file have millions of records.
Before
storing it in table object we have a Lookup Transform that check the Employee
ID into the lookup table and if exists it just same the records into
destination table, others discard it.
We
have another table object named tbl_Grade
Sample Table Object: tbl_Grade
|
EmployeeID
|
Designation
|
|
101
|
A
|
|
102
|
B
|
|
103
|
B
|
|
104
|
A
|
|
105
|
A
|
|
106
|
A
|
Note:
Think that the table object named tbl_Gradealso have millions of records.
So
it is not a wise decision to load the entire table into Lookup transform.
So
according to our needs, we need a parameterized query in Lookup Transform with
Designation. If we specified a designation, the Lookup Transform load those
records only.
But
we want to provide this Designation value by a variable not hard-coded.
So,
it is called a Dynamic Lookup Transform. Hope you understand the case scenario.
So What Solution we can provide
There
is two type of solution that we can provide in this scenario.
1. By
using Cache Transform
2. By
using Dynamic Query in Lookup Transform.
Personally,
I am not preferring the first approach (By using Cache Transform) as we have to
populate the Cache Transform every time with our dynamic SQL statement (When we
change the Department value in our variable).
Second
one is the good approach. But here in this article we are going to discuss both
of them and it’s totally depends on you what you choose as a approach.
Before
jumping into solution, let’s take some sample of flat file and Lookup Transform
table.
Flat File Sample:
Lookup Transform Table Object definition
and Records:
CREATE TABLE [dbo].[tbl_Grade]
(
EmployeeID INT NOT NULL PRIMARY KEY,
Designation CHAR(1) NOT NULL
);
GO
INSERT INTO [dbo].[tbl_Grade]
(EmployeeID, Designation)
VALUES(101, 'A'),(102, 'B'),(103, 'B'),(104, 'A'),
(105, 'A'),(106, 'A');
GO
SELECT * FROM [dbo].[tbl_Grade];
EmployeeID Designation
----------- -----------
101 A
102 B
103 B
104 A
105 A
106 A
Solution -1 [By using Cache
Transform]
In
the above figure we are just storing the Lookup data sets into a Cache
Transform.
In
OLEDB Source editor we are using Data Access mode as SQL Command and the SQL
statement is a parameterized query.
SELECT EmployeeID FROM [dbo].[tbl_Grade] WHERE Designation = ?;
The
value of the parameter is supplied by the variable. Here the actual filtration
of Lookup Transform occurs.
In
Lookup Transform we choose Connection Type as Cache Connection Manager.
Solution – 2 [By using
Dynamic Query in Lookup Transform]
Here
first we just set a Normal Lookup transform as we set it before.
What
we have to do is go to the Control Flow Tab and Select the Property named
Expression of Data Flow Task.
Data Flow Task à Property à Expression à[Lookup].[SQLCommand]
Expression:
"SELECT
EmployeeID FROM [dbo].[tbl_Grade] WHERE Designation ='"+
@[User::v_EmployeeGrade]+"'"
Hope
you like it.
Posted
by: MR. JOYDEEP DAS
