Look up transformation with Full cache Mode Part-1

Drag the Two Data Flow Tasks on Control Flow tab. Rename it as Data Flow Task Cache Connection Manager and Data Flow Task-Look Up Transformation for our reference. Connect the Two Data flow Tasks by using Precedence Constraints. This ensures the cache transformation executes before the execution of Look up Transformation. Otherwise Transformation will be fail. Please find the screen shot below.

Right click on the  First Data Flow Task  you add the cache Connection Manager  and connect to the OLEDB Data Source then they will generate reference data set to Look up Transformation. Please find the screen shot below.

Right click on OLEDB Source, click on Edit, then connect to data source. If not exit create a new connection.  Please find the screen shot below.

Click on New Button Find the next screen shot below.

Click on New Button and connect to Data Source. Please find the screen shot below.

Click on OK Button of connection Manager and then select the Table name from the OLEDB Source Editor and then click OK Button. Please find the screen shot below.

 

 

Next we need to configure the Cache Transform to connect to Cache Connection Manager  and write the data  from connected data source to the cache Connection Manager. Right click on the Cache Transform click on Edit  and Configure the cache connection Manager. Please find the screen shots below.

Click on New Button. Please find  the screen shot below.

 

Next Click on Columns Tab. Now you configure the cache connection manager to specify the column following. Which columns in reference data set are the Index columns. Look up transformation maps columns in input data source only index columns in the reference dataset. You must specify at least one column in as a reference column

 

We are going to specify the Product Id Column as Index column. This is the common column between in the input Data source in the reference Data set. We specify  the Index as  1. Next click on General tab.

 

Next is the reference Dataset there is stored in Memory cache of package runs persist to the file. Saving the cache to file using the cache file and set upping the cache file to write the data to the cache connection managers and able to share data between multiple transformations in the different Packages.

You persist the cache by selecting  Use File Cache and selecting the Cache File from Browse button or you can enter the filename.raw. The raw is the extension for Cache file. Please find the screen shot below.

Click ok Save settings and Check the mappings of  Cache Transform .

Click on Control Flow Tab and click on Dataflow task look up transform. Add Oledb Source , Look up Transformation and OLEDB Destination. First connect to OLEDB Data Source and Look Up Transformation. Please find the screen shot below.

 

Right click on the OLEDB Source and click on edit and then select connection manager. If  connection is not there and then create new connection . In this case I have already created Adventure works connection . I am going to select that connection .  After that  needed to be select Table name. In this case I am going to select Sales.SalesOrderDetails. Please find the screen shot below.

Now we have to configure Look up Transformation. Please find the screen shot below. For that right click on the Look up  click on edit. Please find the screen shot below.