Cache Transformation in SSIS is used to read data from wide variety of sources such as flat files, Excel sheets and ADO.NET data sources and save data from those data sources in .caw file.
By default Lookup Transformation in SSIS uses the OLE DB Connection Manager for 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 and then perform Lookup transformation using the Cache Connection Manager.
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 cache file (.caw extension).
STEP 1: Drag and drop the data flow task from the toolbox to control flow and rename it as Cache Transformation.
Double click on it will open 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 then check mark the First Row has Column Names option.
STEP 3: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
From the above screenshot you can observe that, Country Key is unchecked because we have nothing to do with key columns. We only require 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 havent created the SSIS Cache Connection Manager in this project before, Click on 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: if you want, Provide the description for the Cache Connection manager but for now we are leaving default here.
- Use File Cache: If you check mark this option, the connection manager will write cached data to a file.
- Browse: This button is used to browse the existing file or creating new file of extension .caw
From the above screenshot you can observe that, We used file cache option. Next, we selected the 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 option there is one important property called Index Position.
- Set Index Position to 0 for all the columns that are not used for the lookup. For this example, We don’t want to perform lookup operation on Country Code Column so set the index position for this column to 0.
- Set Index Position to 1 for the columns that are used for the lookup. For this example, We want to perform lookup operations on 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 columns tab to verify that all the columns are mapped exactly or not. If not, please map Input Columns with the appropriate Destination Column otherwise, you will end up with wrong data.
Click ok and run the Cache Transformation in ssis package to create cache file in local drive
Check the result of the SSIS Cache Transformation
Thank You for Visiting Our Blog