I have Source and Dest tables table :source Table : Dest -------------------------------------------------- Col Col ------------------------------------------------- AA aa Bab BAb by default SSIS is Case Sensitive and my Database settings are Case in-sensitive When i Do a lookup on Col in this case, lookpup in SSIS treating both values 'AA' and 'aa' as same .... but i have to treat it as different values, which we can visually see it ...how should i achieve this ? I can achieve this by "Binary_CheckSum",but many blogs says that not 100% assured... MSDN : "BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications."

Hi, The one workaound is to use UPPER() in sql code in source as well as lookup code..If your source/destination is not SQL based you can use derived column transformation to change case of column..- Chintak (My Blog)

Refer this article, http://sqltips.wordpress.com/2007/05/14/case-sensitive-string-comparison-in-sql-server-2005/ and http://www.abstraction.net/ViewArticle.aspx?articleID=72 In source query or in execute sql task you can do it. Regards, YB

Use TRIM() and UPPER() functions on the source key and the match column to avoid any case or whitespace related lookup failures.http://btsbee.wordpress.com/

you can look into the options given in this link : http://geekswithblogs.net/dtotzke/archive/2007/02/06/105585.aspx Set the Lookup Transformation caching option to Partial or None. Setting the cache mode to partial or none causes comparisons to be done by SQL Server. Full cache with pre-formatted data. Update the lookup column using the T-SQL LOWER() or UPPER() functions so that subsequent lookups done by the Lookup Transformation will succeed.Full caching and T-SQL queries instead of tables as your data sources. Use the LOWER() or UPPER() functions on the columns to be compared. This is likely the best of both worlds. Gives you the performance of full caching as well as simplifies your data flow by removing the extra steps needed for option 2. regards joon

Thank you for your replies...........i have gone through all replies My Goal is to treat 'AA' and 'Aa' as different values when comapring in lookup Transformation.

You have not read my previous post completely it seems, follow these steps, Lookup internally does join, lets do the same in our source query (OLE DB Source)In the source query select all columns needed and try to embed this query SELECT ISNULL(myColumn,'n') as myColumn FROM myTable as t1 left outer JOIN myOtherTable t2 ON t1.mycolumn COLLATE Latin1_General_CS_AI = t2.myOtherColumn Put a condition split and redirect the rows with 'n' (lookup rejects)Latin1_General_CS_AI forces join to be case sensitive Hope this will help you. Regards, YB

Thank you for your reply.......small change Source Query: Select Text_Col COLLATE SQL_Latin1_General_CP1_CS_AS From [Source] Lookup Query : Select Text_Col COLLATE SQL_Latin1_General_CP1_CS_AS From [Destiny] Please let me know, if i did anything wrong. Then It Worked......

What you did is correct.Both source query and lookup query are aligned to SQL_Latin1_General_CP1_CS_AS . If you put any filter (where clause) to above query it wont work. I believe my posts helped you. Regards, YB

Thank you for your replies...........i have gone through all replies My Goal is to treat 'AA' and 'Aa' as different values when comapring in lookup Transformation. grdesai, the Look up in SSIS is implemented to be case sensitive out of the box, and your db collation is irrelevant once the data leaves the storage. Arthur My Blog

I have Source and Dest tables table :source Table : Dest -------------------------------------------------- Col Col ------------------------------------------------- AA aa Bab BAb by default SSIS is Case Sensitive and my Database settings are Case in-sensitive When i Do a lookup on Col in this case, lookpup in SSIS treating both values 'AA' and 'aa' as same .... but i have to treat it as different values, which we can visually see it ...how should i achieve this ? I can achieve this by "Binary_CheckSum",but many blogs says that not 100% assured... MSDN : "BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications." Edited: (read the problem other way round) I believe you are using partial/No caching for your lookup which does not loads all the data to memory in one go. This results all the string comparisons to be case in-sensitive due to your database settings. As a work around you can use full caching. This way comparison will be done in-memory, resulting the comparison to be case-sensitive in your case. Hope this helps- Please mark the post as answered if it answers your question

Make the lookup source data to partial cache or no chache. This may solve your problem. http://sqlserverrider.wordpress.com/2012/08/03/case-sensitive-lookup-task-ssis/Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com