Case Sensitivity in SSIS Lookups

Sooner or later you will come across an instance where lookups in SSIS 2005/2008 fail due to attempted matches on values in which do not have exactly the same case. This is because the lookup transform itself is case-sensitive.

A possible solution is to use partial caching which will force the comparison to be made at the database level (assuming your collation is case insensitive). This option will also take a performance hit in most instances.

The best plan is to use the UPPER function in your T-SQL (or a character map transform) for your incoming values. Suggest that the lookup query itself forces uppercase so that you are guaranteed no misses. Jamie Thompson covers this in further detail on his old blog

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: