Read lookup transformation on below link
http://bageshkumarbagi-msbi.blogspot.in/2017/10/lookup-transformation-in-ssis.html
Here we will discuss details in Full Cache mode in lookup transformation and how its work.
This is the most commonly used approach in Lookup Transformation. If we selected this option then, entire lookup (or reference) table will be preloaded into the cache (Memory) and Lookup Transformation will perform a lookup from the Memory instead of Dataset. This works well when we have less number of rows in the lookup table. This is fast.
Here I am selecting Full cache mode
When we execute the package in pre- executing all the data loaded from the full cache.
Now executing the package.
Package executed successfully.
Now see the login progress.
In full cache, all data are loaded in pre-execution phase. Once data is loaded into the cache the package starts executing. Here we will get all details about the reference dataset. How many rows are added to the cache as well size of the data. Here we will get the execution time.
For better understanding, I am looking SQL executing in the SQL Profiler.
Read SQL Profiler here: SQL server profiler
Open the SQL Profiler
When we are running this package see the SQL Profiler
See one-time SQL query is executing and load the data into the full cache.
We can use this cache mode if we don’t want to hit sever again and again. If we are using the production server and the reference database is not a large then it will be better to use the full cache. It has some drawback like it consumes more space if the reference data set it large and our system doesn't have much space, in this case, the package has been failed.
