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

sql sql-server ssis lookup business-intelligence
rrydman picture rrydman · May 11, 2009 · Viewed 9.9k times · Source

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?

Answer

The Lazy DBA picture The Lazy DBA · May 11, 2009

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.

Related questions

SQL/SSIS DataWareHouse Fact table loading, best practices?

I am building my first datawarehouse in SQL 2008/SSIS and I am looking for some best practices around loading the fact tables. Currently in my DW I have about 20 Dimensions (Offices, Employees, Products, Customer, etc.) that are of Type 1 SCD. …

Read More
Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

I'm trying to import a flat file into an oledb target sql server database. here's the field that's giving me trouble: here are the properties of that flat file connection, specifically the field: here's the error message: [Source - 18942979103_txt [424]] …

Read More
SSIS Connection not found in package

I'm kind of new to SSIS programming, and I'm having some problems deploying an SSIS package. This package runs correctly on my PC, does everything it needs to do ... but when I deploy it cannot find the connection strings. Here …

Read More