MSBI Interview Questions

There are basically 3 Cache Modes available in SSIS Lookup Transformation as follows:

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

Linkbar