Lookup Transformation Case Sensitivity Question

Question

Post reply

Lookup Transformation Case Sensitivity Question

Viewing 5 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Paul Williams SSCommitted Points: 1930 More actions · Answer 1

Brandie,

I'm afraid BOL is wrong 😉

Check this out:

http://blogs.conchango.com/jamiethomson/archive/2008/02/12/SSIS_3A00_-Case_2D00_sensitivity-in-Lookup-component.aspx

Paul R Williams.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 2

Paul,

Thank you so much for the link. It is greatly informative. And BOL wasn't the only place I saw the stuff on Full Caching. There are a number of erroneous blog entries and forum threads stating the exact same thing.

Another case of people taking Microsoft's word for gospel. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Paul Williams SSCommitted Points: 1930 More actions · Answer 3

Yes, sometimes access to too much info can be dangerous, I've made the same mistake myself. I discovered the caching issue by chance doing some testing. Just to let you know it's not only my experience but this is a quote from an MS white paper on SSIS connectivity and their documentation isin't always wrong (just wish it would be more consistent ;-):

"The lookup transformations (for example, the Lookup transformation) use reference tables to do value matching during the lookup process. When you use a table as a reference table, the entire table is cached in memory during the transformation by default. For large tables, you can specify partial caching or no caching. This is useful for large lookup tables when loading the entire table would consume excessive memory".

Also, if you are not already aware that partial caching and no caching changes the case-sensitivity of the comparison to that of SQL (which relates back to your original post I think). This is because on full-caching when only using memory all comparisons are done in SSIS. However, with partial and no caching the comparison is now done via a SQL Statement thus takes the case-sensitivity to that of the database (Normally, databases are set to be case-insensitive by default.)

Paul R Williams.

Tim Mitchell SSCoach Points: 15685 More actions · Answer 4

Thanks Paul - I took the case sensitivity thing at face value and just created some workarounds. It's good to know that I can use the cache settings to avoid this problem.

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 5

The things we learn, even when we "think" we know it all. @=)

Ditto on the thanks, Paul. You're a good source of info.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.