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!

Data Solutions Architect at Indicia. More years than I care to remember wrangling SQL Server and .NET. Data geek, football fan, and Xbox gamer. Check out my other blog Press B To Parent if you're a gamer.

Leave a Reply

Next ArticleMapping C# DateTime to SQL Server datetime2 via SSIS