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

Qns by Labels	Differences	SQL Server	SSIS	SSRS	T-SQL

__

Post a Job! $5 for 60 days

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