Overlooked Properties in SSIS: Lookup Component Cache Mode

SSIS has so many properties that some are overlooked or ignored. In this article we will take a closer look at one of them: the “cache mode” setting of the Lookup Component.

As the name indicates, the “Cache mode” lets you choose which form of caching the Lookup component will use. The available modes are Full, Partial and None. By default “Full Cache” mode is selected. This is a good default value, but there are of course situations in which you might want to change it, otherwise there would be no need for the other modes.

Let’s have a look at how the different modes affect the behavior of the parent component. Once we know the impact, it will be easier to decide which mode to use in a given situation.



Full Cache

The entire lookup dataset is loaded into SSIS cache. Your dataflow waits until the cache is fully loaded before starting to do its lookups. This is the default setting, and it should be. For most situations this is the best setting. The only 2 situations when this setting shouldn’t be used, is when the Lookup data set is very large, or when the number of input records is really small compared to the lookup dataset.

Let’s combine these two cases in an example. Consider the situation in which your input dataset is only 2 records large. But the lookup dataset is over 200 million records. Using Full cache mode, SSIS loads the full 200 million records in memory, and then performs the lookup. All this, just to look up 2 values. A bit of overkill, isn’t it?

In this specific case, it would be much better to use the “No Cache” mode.

No Cache

The easiest of the bunch. SSIS doesn’t keep any cache and sends a query to the reference DB for every record it needs to look up.

When to use?

  1. The number of records in the source data set is very small (and will remain so)
  2. AND the reference data set is very large (otherwise just use full cache mode)

Returning to the extreme example above, the “No Cache” setting solves all our problems. Instead of loading 200 million records in memory and doing the lookup there, SSIS queries the DB only twice, once for each row in the input data source. This is a huge gain in performance and server load.

Partial cache

Of course, there has to be a third situation, for which neither of these modes offers a good solution. Why else would we have 3 modes to select from? The third mode is a hybrid of the other two.

Let’s return to the example above, but increase the size of our input dataset to 10 000 rows. Investigating our input data we notice that although we now have 10 000 records, the number of unique values that need to be looked up is really small (let’s say 10). So we have thousands of input records and millions of records in our lookup dataset of which we only really need 10. If we would use “No Cache mode” we would query the DB for each record in the input dataset. Thus we would perform 9 990 useless lookups. Not an ideal solution by any means.

Luckily we have the Partial Cache mode to help us out in this situation.

With Partial Cache enabled, SSIS builds up a cache as it goes along. For each record SSIS checks if the value to look for is already known in its cache. If that value is not there, SSIS queries the reference DB and stores the result in its cache for future queries.

This way SSIS only retrieves the lookup records it really needs.

When to use?

  1. The number of unique lookup combinations in the source data set is small
  2. AND the reference data set is very large (otherwise just use full cache mode)

As you can see, this is a perfect fit for our previous example. Though we have 10 000 input records, we only query the reference DB ten times at most. Once we have retrieved the lookup for a specific value, all further records with that value will use the cache.


Using the correct cache mode for the job can have a huge impact on performance and server load. While the default setting is correct most of the time, always keep in mind what it does, and change it if necessary.

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...