Saturday 28 November 2015

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

3 comments:

  1. yaar your are really great and you improved the tech brohter solution

    ReplyDelete
    Replies
    1. NO boss.. I am not so. Tech Brother is a big name

      Delete
    2. Hi Rohit I am reading blogs from last 2 Year. I thing its Joydeep own way of explanation and writing.

      Delete