SSIS – Dynamic Lookup Transform

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
…..

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