What is Full Cache, Partial Cache and No Cache of Lookup Transformaton in SSIS Package

How to Handle the Row yielded no match during lookup and Row disposition Errors in SSIS Lookup Transformation
Scenario :
Suppose we have the Source data of Sales Orders details as follows..

Out of these Sales Orders, few of them are got Cancelled , and those Orders details are available in a Lookup Table which is in the SalesDB as follows...

 SELECT [Order_Id]
    ,[Region_Code]
    ,[Order_Status]
   FROM [SalesDB].[dbo].[LKP_Orders_Status]
   WHERE [Order_Status]='Cancelled' 

Now we need to Loop the Source data against this Lookup Table, then we need to send the Output to the Destination Tables which exist in the MyAnalyticsDB as follows..

The Cancelled Orders data should load to Tbl_Orders_Cancelled , and other Orders data should load to Tbl_Orders

Now I will design the Package to Fulfill the above scenario

--------------------------------------------------------------------------------------------------------------

Phase-1 : Add the Data Flow to Control Flow and then Design the Flow
--------------------------------------------------------------------------------------------------------------

A) Add the Data Flow to Control Flow:

B) Add the Flat File Source in the Data Flow and Connect to Source:

--------------------------------------------------------------------------------------------------------------

Phase-2 : Add the Lookup Transformation to Data Flow and Connect to the Source and then Set the Properties
--------------------------------------------------------------------------------------------------------------

A) Add the Lookup Transformation Inside Data Flow and Connect to Source:

B) Next go to Lookup Transformation Editor > Connection Tab; Make the connection to the Lookup Table and Select the required Columns.

The Lookup transformation supports the only the OLE DB connection manager.
The reference dataset(Lookup Table) can be a cache file, an existing table or view, a new table, or the result of an SQL query. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset

C) Next go to Lookup Transformation Editor > General Tab; Select the Cache Mode

Full Cache :
When we select the Cache Mode as "Full Cache" then Lookup Transformation will read all the Data once from Source into the Cache, then it will lookup each record from Source against the Lookup Table, If matches found those records will be load to Lookup Match Output and non match records will be load to Lookup No Match Output.

Partial Cache :
When we select the Cache Mode as "Partial Cache" then Lookup Transformation will read each record from the Source into the Cache, then it will lookup that record from Source against the Lookup Table, If matches found that source record will be stored in the Cache for further use(next time it will look in Cache first) and then load that record to Lookup Match Output and non match records will be load to Lookup No Match Output.

In the Partial Cache case, we need to Allocate the Cache memory size for Lookup Matching and No Matching. The memory will be allocated to Cache from your system memory.

Note :
The rows with matching entries in the reference dataset and, optionally, the rows without matching entries in the dataset are stored in Cache. When the memory size of the cache is exceeded, the Lookup transformation automatically removes the least frequently used rows from the Cache.

You can allocate the Cache from Lookup Transformation Editor > Advanced Tab

No Cache :
When we select the Cache Mode as "No Cache" then Lookup Transformation will not use the Cache. It will read the each record directly Source data then it will lookup that record  against the Lookup Table,then it will load that record to Lookup Match Output and non match records will be load to Lookup No Match Output.

Notes :
Whether you select partial cache or no cache, you use an OLE DB connection manager to connect to the reference dataset. The reference dataset is generated by using a table, view, or SQL query during the execution of the Lookup transformation.The rows with matching entries in the reference dataset and, optionally, the rows without matching entries in the dataset are stored in cache. When the memory size of the cache is exceeded, the Lookup transformation automatically removes the least frequently used rows from the cache.

D) Next go to Lookup Transformation Editor > Columns Tab ; Map the Source key column with Lookup Column in Lookup table

Here we will lookup Order_Id column from source against the Lookup Table column "Order_Id"

Next you can select any other columns from Lookup table if you want to read into Output.

Here I am reading the Region_Code and Order_Status from Lookup table.

E) Next go to Lookup Transformation Editor > General Tab; Specify how to handle rows with No Matching entries..

Here we need to specify how you need to handle the rows with now Match output.

If you choose the option "Fail Component" , then run the Package, it will throw the following errors when it found the No Matching records from Source.

Error : Row yielded no match during lookup.

[Lookup Cancelled Orders [45]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Lookup Cancelled Orders" failed because error code 0xC020901E occurred, and the error row disposition on "Lookup Cancelled Orders.Outputs[Lookup Match Output]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

To avoid these Errors we need to specify the option "Redirect rows to Match output" as follows..

Next Say OK

--------------------------------------------------------------------------------------------------------------

Phase-3 : Next Connect the OLED Destinations to Lookup Transformation to Load Matching and No Matching Outputs
--------------------------------------------------------------------------------------------------------------

A) Connect the OLEDB Destination to Lookup Transformation, for loading Matching Output

B) Next go OLEDB Destination Editor > Connection Manger Tab , then set the Connection and select the Destination Table

C) Next go OLEDB Destination Editor > Mapping Tab , then do the Source and Destination Columns mapping

Next Say OK

D) Connect the OLEDB Destination to Lookup Transformation, for loading No Matching Output

E) Next go OLEDB Destination Editor > Connection Manger Tab , then set the Connection and select the Destination Table

F) Next go OLEDB Destination Editor > Mapping Tab , then do the Source and Destination Columns mapping

Here Region_Code and Order_Status from lookup table will not flow into No Matching Output.

Next Say OK

G) Finally Run the Package

--------------------------------------------------------------------------------------------------------------

Output :

Matching Output Destination Table

Select * From [dbo].[Tbl_Orders_Cancelled]

No Matching Output Destination Table

Select * From [dbo].[Tbl_Orders]

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------