Cache transformation in SSIS

The Cache Transform transformation generates a reference dataset for the Lookup Transformation by writing data from a connected data source in the data flow to a Cache connection manager. The Lookup transformation performs lookups by joining data in input columns from a connected data source with columns in the reference database.

We can use the Cache connection manager when we want to configure the Lookup Transformation to run in the full cache mode. In this mode, the reference dataset is loaded into cache before the Lookup Transformation runs.

It is used to read data from the wide variety of sources such as flat files, Excel she, ts and ADO.NET data sources and save data from those data sources in the .caw file.

We know that in the lookup transformation we can use either cache connection or OLEDB connection.

                          

Suppose our reference data in is file then in this case we need to use the cache transformation and read the file and store these data in the case .caw file. .caw file is nothing but it is a data set.

In the demo, I am using the source as the CSV files.

Taking data flow task and source as the flat file

  

Now taking transformation as cache transformation.

 

Go to column table and create position index

 

Click ok.

Do the mapping and click on the ok.

 

Now cache transformation configured successfully.

 

When we run this package a cache file is created on the specified folder.

 

The cache file is created. This file we can’t read by the opening.

 

Using cache file in lookup transformation.

Taking loop up transformation.

 

Now we need to create the configuration.

In general table, we need to select the cache connection manager.

  

Now go the connection tab.

  

Configure the cache connection manager.

Now go to the column tab am do the mapping.

 

In this way, we can use the .caw file in the lookup transformation.