SSIS Lookups are Case Sensitive

Lookup's are a key component in SQL Server Integration Services (SSIS).

Let's say we want to lookup data from the personalookup table. In this example, we assume that we have a record in the personlookup table with the name jim.

In our source we have JIM, jim and JiM. Ideally all of these records should match with the existing record of jim. Below is the outcome if we run this package:

You can see that only one records was matched. If you add a data viewer to the success path, you will see that only the jim record was matched.

There is a workaround to overcome this issue. The workaround is to convert the data to lower case or upper case.

In the modified package shown in the above image, the derive column is introduced to set the source data to lower case. In the Lookup data flow task the following command is included to set the case to lower.

SELECT lower(NAME) NAME FROM Personlookup