Cache Transformation in SSIS is used to read data from a wide variety of sources such as flat files, Excel sheets, and ADO.NET data sources. And then save data from those data sources in .caw file.
By default, the Lookup Transformation uses the OLE DB Connection Manager for the lookup reference table. We can use other sources also, but they must be used indirectly via SSIS Cache Transformation.
In real-time, We use the SSIS Cache Transformation to save the reference table in .caw format. Then perform Lookup transformation using the Cache Connection Manager in SSIS.
Cache Transformation in SSIS Example
In this example, we are going to use the SSIS Cache transformation to copy the data present in the Excel file to the cache file (.caw extension).
STEP 1: Drag and drop the data flow task from the toolbox to the control flow and rename it as Cache Transformation.
Double click on it opens the data flow tab.
STEP 2: Drag and drop Excel source from toolbox to data flow region. Double click on it to configure the excel file
Click on the Browse button to select the source file from the local hard drive.
From the above screenshot, you can observe that we selected the COUNTRIES LOOKUP.xls file in local drive.
NOTE: If your Excel file contains column names in the first row, checkmark the First Row has Column Names option.
STEP 3: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns.
From the above screenshot, you can see that the Country Key unchecked because we have nothing to do with key columns. We only require the Country name to use as lookup column and Country code column as additional output column.
Drag the blue arrow from Excel Source to Cache Transformation to perform the transformation on the source data.
STEP 4: Double click on the SSIS Cache Transformation to configure it.
We haven’t created the SSIS Cache Connection Manager in this project before, Click on the new button to configure it.
STEP 5: On the General tab of the SSIS Cache Transformation Editor,
- Connection Manager Name: The Default value is Cache Connection Manager. But you can change the name of the connection manager as per the project requirement.
- Description: Provide a valid description for the Cache Connection Manager. But for now, we are leaving default here.
- Use File Cache: If you checkmark this option, the connection manager will write cached data to a file.
- Browse: This button is used to browse the existing file or creating a new file of extension .caw
From the above SSIS cache transformation screenshot, you can observe that We used the file cache option. Next, we selected lookup.caw file located in my local drive.
Next, select the SSIS Cache Transformation Column Tab.
STEP 6: Columns tab will show the list of available columns coming from the source data and their data types, length, precision scale, etc. Apart from these options, there is one important property called Index Position.
- Set Index Position to 0 for the columns not used for the lookup. For this example, We don’t want to perform lookup operation on the Country Code Column. So set the index position for this column to 0.
- Set Index Position to 1 for the columns used for the lookup. For this example, We want to perform lookup operations on the Country Name Column. So set the index position for this column to 1.
Click ok to finish configuring the SSIS Cache Connection Manager.
STEP 7: Click on the SSIS cache transformation columns tab to verify that all the columns mapped exactly or not. If not, please map Input Columns with the appropriate Destination Column. Otherwise, you will end up with the wrong data.
Click ok and run the SSIS Cache Transformation package to create cache file in local drive
Check the result of the SSIS Cache Transformation
