How can I get SSIS Lookup transformation to ignore alphabetical case?

Question

Hopefully this is easy to explain, but I have a lookup transformation in one of my SSIS packages. I am using it to lookup the id for an emplouyee record in a dimension table. However my problem is that some of the source data has employee names in all capitals (ex: CHERRERA) and the comparison data im using is all lower case (ex: cherrera).

The lookup is failing for the records that are not 100% case similar (ex: cherrera vs cherrera works fine - cherrera vs CHERRERA fails). Is there a way to make the lookup transformation ignore case on a string/varchar data type?

Solution 1

There isn't a way I believe to make the transformation be case-insensitive, however you could modify the SQL statement for your transformation to ensure that the source data matches the case of your comparison data by using the LOWER() string function.

Solution 2

Set the CacheType property of the lookup transformation to Partial or None.

The lookup comparisons will now be done by SQL Server and not by the SSIS lookup component, and will no longer be case sensitive.

Solution 3

You have to change the source and as well as look up data, both should be in same case type.

Solution 4

Based on this Microsoft Article:

The lookups performed by the Lookup transformation are case sensitive. To avoid lookup failures that are caused by case differences in data, first use the Character Map transformation to convert the data to uppercase or lowercase. Then, include the UPPER or LOWER functions in the SQL statement that generates the reference table

To read more about Character Map transformation, follow this link"

• Character Map Transformation

View more solutions

Comments

[rrydman]

Perfect solution - I put a derived column transform before my lookup that just applied LOWER() to the column. Works great :-)

[John Sully]

This worked perfectly for my application, plus it has the advantage of being easy.