THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Data Platform MVP from the Netherlands

SSIS - Lookup is case sensitive

You can read this blog post at this link: https://jorgklein.com/2009/04/14/ssis-lookup-is-case-sensitive

This blog has moved to www.jorgklein.com There will be no further posts on sqlblog.com. Please update your feeds accordingly.

You can follow me on twitter: http://twitter.com/jorg__klein

Published Tuesday, April 14, 2009 4:16 PM by jorg

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Amit said:

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

May 27, 2015 6:25 AM
 

Seb Thirlway said:

I like Amit's solution.  My approach is: store the data in the dimension just as it comes in (whatever case).  But transform it (and the input data) to UPPER for the purposes of comparison (lookup).

If you're unlucky enough to have SQL running with a case-sensitive collation order, then all bets are off...

The exception to this approach is trailing/leading spaces.  I don't want that nonsense in my dimension, so I LTRIM(RTRIM( the input before saving it to the dimension.  This does mean the input fact data has to be LTRIM(RTRIM(-ed for the lookup.

August 3, 2017 3:05 AM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Data Platform MVP from the Netherlands.
Privacy Statement