Why is the SSIS Lookup Component Case Sensitive (or isn’t it)?

Many websites and blog posts will tell you that the SSIS Lookup Component is Case Sensitive (CS). And while they are correct that there isn’t a direct property to change the case sensitivity of the component, it can however be used in a non-case sensitive way.

How to make it Case Insensitive (CI)

For those of you looking for a quick answer: by setting the lookup component’s cache mode to Partial Cache or No Cache you can make the lookup component use the case sensitivity setting of the database.

Check the property window of your database if you want to know the case sensitivity setting.

Why does the cache mode affect the case sensitivity (CS)?

Still reading? Great! Consider yourself part of the small group of readers who isn’t content with the how, but also want to know the why. The reason SSIS defaults to CS is found within the internal engine of SSIS. More specifically, in the way SSIS creates Cache files. When the lookup component is set to Full Cache mode, SSIS retrieves the entire lookup table, stores it in-memory and does the comparisons there. When doing the comparisons in-memory, SSIS uses the .NET string comparison function which is case (and space) sensitive by default. With the Lookup set to Partial or No Cache SSIS hands the comparison to the database engine, which uses its local DB settings to do the comparison. So if your lookup DB’s collation is set to case insensitive(CI) and you use Partial or No Cache, the lookup is case insensitive.


In SSIS the default setting for a Lookup Component’s Cache mode is Full Cache. And in most cases, this is the best setting. But it is just a setting, and can – and sometimes should – be changed

Any drawbacks?

Now that you know how and why, there is only one question remaining. Should I…

Disabling the cache can have a serious impact on the performance of your package. Especially if you have a lot of records entering your lookup. So even though you may want to do a case insensitive lookup, disabling the cache might not be the best solution. Should performance reasons demand a Full Cache mode (they often will), you can always use the old but tried workaround of using the UPPER or LOWER function on both the input and the reference query to fake a “Case insensitive” lookup.


While it is possible to make the lookup component case insensitive, it often won’t be a practical solution. But at least now you know why it is case sensitive by default. For more information on the different cache settings, have a look at my previous post: Overlooked Properties in SSIS: Lookup Component Cache Mode.

Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all the fun and exciting new...
Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature
If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before....
Creating maps with R and Power BI
The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you are creative and have knowledge about the...
Reading monitoring alerts in Telegram
Sending monitoring alerts through Telegram
What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the...
Send mails with Azure Elastic Database Jobs
The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want similar functionality in Azure SQL...
Sorting matrices in Power BI
Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour...