Case sensitivity in SSIS Lookup Transformation

Something that catches out a lot of new SSIS developers.  The caching mode used for a Lookup Transformation may affect case sensitivity.

The Full Cache option is case sensitive by default.  Partial and No-cache options use the Collation setting of the database (or table) to handle case.

Of course, using Partial or No-caching results in the SSIS package issuing a call to the database for each row in the data flow, so use them wisely!

Solutions Architect at Indicia. More years than I care to remember wrangling SQL Server and .NET, now mainly AWS. Data geek, football fan, and Xbox gamer. Check out my gaming blog if you're into that sort of thing.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleMapping C# DateTime to SQL Server datetime2 via SSIS