There are mainly three different types of Lookup Cache Modes present in SSIS Lookup Transformation :
Full Cache Mode : This type of cache mode helps SSIS query the database before the beginning of the data-flow task execution. This mode is a critical part of the pre-execute phase. Besides, SSIS copies all the data from the reference table (or lookup table) into the SSIS lookup cache during full cache mode.
Partial Cache Mode : This cache mode helps SSIS to query the database against new rows from different sources. In this mode, the row is cached into the SSIS lookup cache only in the case when there is a subsequent match. Once the cache gets full, SSIS automatically starts removing existing rows based on the match and usage stats. After that, new matching rows are loaded into the lookup cache.
No Cache Mode : As the name suggests, SSIS doesn't cache any rows in this cache mode unless there are two subsequent sources with the same lookup values. In 'No Cache Mode', the database is queried to get the match data/value from the reference table for each row coming through the source.
