No cache mode in Lookup transformation in SSIS

Read lookup transformation on below link

Lookup transformation in SSIS

Here we will discuss details in No cache mode in lookup transformation and how its work.

 If we selected this option then, Lookup Transformation will not use cache to store the lookup table at any stage. When a new row comes from the data flow, the Lookup Transformation directly check in the lookup table for the matching values.

Here I am selecting No cache mode.

Now I am executing the package

In the no-cache mode, every time hit the reference dataset.

For better understanding, I am looking SQL executing in the SQL Profiler.

Read SQL Profiler here: SQL server profiler

http://bageshkumarbagi-msbi.blogspot.in/2017/07/sql-server-profiler.html

Open the SQL Profiler

See it in the profiler

  

For the every record SQL query executed in the reference dataset. It is very slow.