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




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |