SSIS - Lookup Transformation

Source:Lookup Transformation In-Depth SSIS 2008r2, 2012 (Full ,Partial, No cache) modes


Note:

Full Cache mode:->

1.The lookup table all data gets loaded to the memory in preexecution phase, so there is no query to look up table.

2.If lookup table too big , computer need to have enough memory.

3.If look up table has duplicates,it will keep the unique rows.

4.Then source data query runs against the rows in memory.

5.Look Up table ~ Source Table ( if Lookup table small -  then it is better option)

Partial Cache mode:->

1.Source data all get loaded to memory.

2.In the advance tab - the no matched rows are stored here.

3.So when a source query runs, when it finds match it stores in the memory and as it stores the unique
values of the look up , after a while look up table unique values stays , so no more query to look up table.

4.If Look up table is too big - then it is a better option.

No cache mode:->

1.The dynamic sql runs for each row in source.

2.If source is small then it is fine.




Partial

Data Flow:

Data Flow Properties:

DefaultBufferMaxRows:10000
DefaultBufferSize:10485760

Lookup Transformation in SQL Server Integration Services (SSIS)