Partial cache mode in lookup transformation in SSIS

Read lookup transformation on below link

Lookup transformation in SSIS

Here we will discuss details in Partial Cache mode in lookup transformation and how its work.

If we selected this option then, Lookup Transformation starts the transformation with an empty cache. When a new row comes from the data flow, the Lookup Transformation first checks for the matching values in its cache. If it is not found in the cache then it will check in the lookup table. And if no match is found, it queries the lookup table. If the match is found in the lookup table then, the value will be cached (stored in the memory) for the next time. If the lookup table is very big then we can use this approach. We can set the size of the partial cache.

Here I am selecting Partial cache mode

  

When we are selecting the Partial cache mode the Advance tab comes in picture.

Now we need to select the Advance tab and set the buffer size.

By default cache size is 25 MB. We can increase or decrease the size of the cache. If we want the store the no match records in this case we need to check the Enable cache for rows with no matching entries.

If we want to write our one custom query then we need to check the Modify the SQL statement and write our own SQL script.

Data is not loaded in a cache in pre-execution phase.

See now I am executing the package.

  

See the pre executing phase data is not loaded in cache. In full cache, mode data is loaded in pre-execution phase.

Now we can see the operation in SQL server profiler.

Read SQL Profiler here: SQL server profiler

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

Open the SQL Profiler

  

Dynamic SQL query hit the reference database and fetch that record from reference dataset and tore that records in the cache.

Dynamic SQL query is nothing but we can see in the advance tab.

  

Question marks (?) except AddressID. See it in sql profiler.