Jorg Klein has just written a useful blog entry about the SSIS Lookup component entitled "SSIS – Lookup Transformation is case sensitive". Jorg correctly observes that:
A lookup transformation uses full caching by default. This means that the first thing it does on execution, is loading all the lookup data in its cache. When this is done it works as expected, but with case sensitivity.
Jorg suggests a workaround of using a different CacheType:
The solution is to 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.
This will (usually) work but I want to point out a couple of pitfalls here:
- It will only work if the SQL Server database that the Lookup component is connecting to is using a case-insensitive collation
- This technique issues a query against the database for every single row in the pipeline and hence it will usually be an order of magnitude slower than using CacheType=Full. [N.B. Please read Paul Williams' comment below where he gives a great explanantion of why I chose to use the word "usually" here.]
For these reasons changing the CacheType property isn't a technique that I ever employ. Instead I use Jorg's other suggested workaround:
Another solution is to format the data before you do the lookup. You can do this using the T-SQL LOWER() or UPPER() functions. These functions can be used in a query or for example in a derived column SSIS component.
In that last sentence Jorg suggests changing the case in EITHER the lookup cache OR the pipeline data. I would go further than that, I would change the case in BOTH places so as to completely avoid the chance of failed cache hits.
Thanks to Jorg for raising an important issue because I daresay that the majority of SSIS developers have been stung by this at some point. The lack of a case-insensitive Lookup component in SSIS bothers me greatly. If you want to add your weight to the campaign for a case-insensitive Lookup component then go here to vote and voice your opinion.