Cache Connection Manager in SSIS

The Cache Connection Manger in SSIS, is used in the Cache Transformation. Lookup Transformation uses this connection manager to perform lookup operations using the cache file. Please refer Lookup Transformation in Full Cache Mode article to understand, How to use Cache file in Lookup Transformation.

Cache Connection Manger in SSIS performs 2 operations:

• Cache Connection Manger reads data from the Cache Transformation and save the data in cache file with extension of .caw.
• Cache Transformation reads data from the cache file using Cache Connection Manger

Configuring Cache Connection Manager in SSIS

In this example We are going to show you, How to create or configure Cache Connection Managers in SQL Server Integration Services (SSIS)

Once you created new Project under SSIS. If you look at the Solution explorer we have three folders. For now lets 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 form will be opened to select the connections managers from the list it is provided.

Here we selected the Cache connection manager from the list. Once we selected the Cache connection manager, another window form of Cache Connection Manager Editor will be 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 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 provide the valid description about 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 check mark this option then the browse button and file box will be opened to browse required file.
• File name: Cache File path and 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 select the already existing cache file or else you can create 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 Manger. Now, Click on the Browse button to select the Cache file.

Once you click on the Browse button a new window will opened to select the existing cache file or create new file.

From the above screenshot you can observe that, we already have one cache file in the folder. To create new one just type the name of the file at File name as we done above (Lookup File). This will create 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 cache connection manager. Now we have to configure the column tab

Index Position: We have to specify the Index position of each and every column present in the cache file. Index position will be 0 for all the non-index columns. For all the index columns, index position will be positive number.

This is the most important property for the Lookup Transformation because Lookup Transformation will perform lookup on the columns with positive index. For this example Country Name column has unique values so we changed the index position to 1 for this column.

Click ok button to finish configuring the Cache Connection Manger.

From the above screenshot you can observe that, We successfully created Cache Connection Manager in SSIS.

Thank You for Visiting Our Blog