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.

Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...
How to easily parse JSON in Power BI Desktop
Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want...
Power BI reports as monitoring tool
Who has not created monitoring reports or beautiful dashboards in Power BI? Would it not be great to show off...
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...