Popular Products from Microsoft BI Stack - Try Today!
- advertisement -

What are the Different Lookup Cache Modes Available in SSIS? Explain Each One

» July 4, 2010 | |

Qns by Labels

There are basically 3 Cache Modes available in SSIS Lookup Transformation as follows:
  1. Full Cache Mode
  2. Partial Cache Mode
  3. No Cache Mode
  • Full Cache Mode: In this cache mode, SSIS queries the database before the start of the data flow task execution (pre-execute phase) and loads all the data from lookup / reference table into SSIS lookup cache. Following are few considerations while using Lookup in Full Cache Mode:
    • Size of available memory / cache should be taken into consideration before choosing this option.
    • Use this mode when the reference table is small OR
    • Use this mode when only a subset of reference table is loaded into lookup (using filter conditions in SQL command).
  • Partial Cache Mode: In this cache mode, SSIS queries the database against new rows coming in from the source and if there is a match then that row is cached into SSIS Lookup Cache for rows coming subsequently in the data flow which might have a match. When the cache becomes full, then SSIS removes few of the rows from cache based on the usage / match statistics for those rows and loads the new matching rows into the Lookup Cache. Following are few considerations while using Lookup in Partial Cache Mode:
    • Use this mode when the source data set or data to be processed is small OR
    • Use this mode when the reference table is large and only few of the records from the reference table will be used / will match against the source data rows.
  • No Cache Mode: In this cache mode, SSIS does not cache any rows into Lookup Cache (except for cases like two subsequent source data rows have a match with same lookup values etc). For every row coming from the source, the database is queried to get the matching value / data from the reference table. Here are few considerations while using Lookup in No Cache Mode:
    • Use this mode when less number of rows are being processed OR
    • Use this mode when the reference table is very frequently modified (Insertion / Updation / Deletion of records).
Bookmark and Share

6 comments:

Anonymous,  February 18, 2013 at 4:18 AM  

Piyush Group is today one of
the leading real estate developers of the country.

Anonymous,  February 18, 2013 at 5:27 AM  

Piyush Group is
today one of the leading real estate developers of the country.

Anonymous,  February 18, 2013 at 8:45 AM  

Piyush Group is today one of
the leading real estate developers of the country.

Anonymous,  June 12, 2013 at 5:53 PM  

Greetings from Colorado! I'm bored to death at work so I decided to check out your website on my iphone during lunch break. I really like the info you provide here and can't wait
to take a look when I get home. I'm surprised at how quick your blog loaded on my phone .. I'm not even using WIFI,
just 3G .. Anyways, awesome site!

Look into my page http://beltbucklesexpert.com

Anonymous,  June 12, 2013 at 9:08 PM  

Saved as a favorite, I love your website!

Here is my site :: Kiteash6.webgarden.com ()

Post a Comment

Back to TOP