The Cache Connection Manager in SSIS used in the Cache Transformation. Lookup Transformation uses this connection manager to perform lookup operations using the cache file.
Cache Connection Manager in SSIS performs 2 operations:
- Cache Connection Manager reads data from the Cache Transformation and saves the data in the cache file with an extension of .caw.
- This Transformation reads data from the cache file using Cache Connection Manger
Please refer to Lookup Transformation in Full Cache Mode article to understand, How to use the Cache file in Lookup Transformation in SSIS.
Configuring Cache Connection Manager in SSIS
In this example, we are going to show how to create or configure Cache Connection Managers in SQL Server Integration Services (SSIS)
Once you created a new Project under SSIS. If you look at the Solution explorer, we have three folders. For now, let’s concentrate on the Connection Managers Folder.
Right-click on the Connection Managers folder present in the solution explorer and select the New Connection Manager option from the context menu.
When you click on the New Connection Manager option, an Add SSIS Connection Manager window opened to select the connections managers from the list.
Here we selected the Cache connection manager from the list. Once we selected the Cache connection manager, another window of Cache Connection Manager Editor opened to configure the Cache connection manager.
General Tab
General tab in the Cache Connection Manager Editor box provides an option: whether you want to save the data into a cache file. Or you want to read the data inside the cache file. Following are the options available in the general tab
- Connection manager name: Please provide the name for the Cache Connection Manager as per the company requirements.
- Description: Please describe the connection. This information will be useful for the others to understand what this cache connection doing
- Use file cache: Please check mark this option to use the cache file. If you checkmark this option, the browse button, and the file box will be opened to browse the required file.
- File name: Cache File path and the file name will be displayed here. (Or address of the cache file)
- Browse: Once you click on this button, it will open a new window to select the required file name. Either you can choose the already existing cache file, or else you can create a new cache file.
- Refresh Metadata: Deletes the old metadata of the columns and then regenerate new metadata.
From the below screenshot, you can observe that we changed the Name and Description of the Cache Connection Manager. Now, Click on the Browse button to select the Cache file.
Once you click on the Browse button, a new window will open to select the existing cache file or create a new file.
From the above screenshot, we already have one cache file in the folder. To create a new one, type the name of the file at Filename as we did above (Lookup File). It will create a new cache file.
Now, Let us see how to select the existing file
Click on the open button to select the cache lookup.caw file.
We finished selecting the existing cache file using the cache connection manager. Now we have to configure the column tab
Index Position: We have to specify the index position of each column present in the cache file. The index position will be 0 for all the non-index columns. For all the index columns, an index position is a positive number.
It is the most important property for the Lookup Transformation because Lookup Transformation will perform the lookup on the columns with a positive index. For this example, Country Name column has unique values. So we changed the index position to 1 for this column.
Click the ok button to finish configuring the Cache Connection Manager.
From the above image, you can see we successfully created Cache Connection Manager in SSIS.