SSIS – Case IN Sensitive Match by Lookup Transform

Introduction

Data source is not perfect. Sometime the simple scenario makes more completed that we imagine. Here in this article we are demonstrating the Lookup match in case insensitive way. Hope you find it interesting and it put some value in your professional career.

The Scenario

We have two table in our database one is [tbl_EmployeeDetails] which contains the employee name.

EmpName

joydeep

rAjesh

DEEpasree

If we look at the data carefully we find that the Employee name case is sometimes Upper or Lower or both.

We have a Lookup table with the name [tbl_SelectedEmployee] which contains selected employee name.

EmpName

JOYDEEP

As we all know very well that the Lookup is Case sensitive and when we match both it not works. So the question is how we solve it

The Control Flow Tab

Run with Normal Scenario

It is unable to match.

How to Solve it

Solution -1 [ Using UPPER clause ]

We can put the UPPER clause in Select Statement of OELDB Source

SQL Command

SELECT UPPER(EmpName)AS EmpName

FROM   [dbo].[tbl_EmployeeDetails];

SQL Command

SELECT UPPER(EmpName)AS EmpName

FROM   [dbo].[tbl_SelectedEmployee];

Works fine when we run it.

It is NOT a GOOD solution as per my opinion. It is just unnatural use of component.

Solution – 2 [ Just change the Cache Mode of Lookup ]

If we Choose the Full Cache it is Case Sensitive and if we choose the Partial Cache is not Case sensitive.

Hope you like it.

Posted by: MR. JOYDEEP DAS