Home > SSIS, SSIS 2008, SSIS Tasks > Case insensitive comparison in SSIS 2008 LookUp transformation

Case insensitive comparison in SSIS 2008 LookUp transformation

Developers using SSIS 2005, were never satisfied with the lookup component . The lookup component in ssis 2005 didn’t support case insensitive comparison.  This always resulted in missing some records in the output or even used to cause the failure of the lookup trasformation.

However in SSIS 2008, we now can do a case insensitive comparison.  This is done by setting the “Cache Mode” property of the lookup to nocache ( refer pic ). Setting this property results in executing the comparison on sql server directly, this would result in case insensitve comparision( provided the DB uses case-insensitive collation, which is the default setting of Sql Server). Setting the “CacheMode” to full cache will result in loding the query result to cache & then performing a case sensitive comparison.

nocache

After setting the “CacheMode”, you need to specify the OLEDB Connection manager and the Query/table name .

Rest of the execution proceeds as it was in SSIS 2005.

An alternate way of performing case insensitive comparison is by using a query at both source and the lookup using the functions like upper() and lower() of transact SQL. Convert the fields to compare ,into UPPERCASE and then  run the lookup.

The third way of doing it is by using “Character Map” Transformation , which will allow you to change the case of a string.

CaseInsensitive

In the 2nd & 3rd case , one need not worry about collation :).

Advertisements
Categories: SSIS, SSIS 2008, SSIS Tasks
  1. Julia Benedict
    December 3, 2010 at 12:39 am

    Nice article. Waiting for you to continue the topic.

    Julia Benedict
    independent escort warsaw

  1. May 28, 2012 at 4:54 pm

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: