8

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?

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
rrydman
  • 501
  • 3
  • 9
  • 15

4 Answers4

15

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.

The Lazy DBA
  • 1,492
  • 1
  • 13
  • 14
  • Perfect solution - I put a derived column transform before my lookup that just applied LOWER() to the column. Works great :-) – rrydman May 11 '09 at 20:14
12

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.

mpowrie
  • 603
  • 8
  • 14
1

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

Rory McCrossan
  • 331,213
  • 40
  • 305
  • 339
amal
  • 11
  • 1
1

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"

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41