Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Cache Connection Manager in SSIS

by suresh

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.

Cache Connection Manager in SSIS 1

Right-click on the Connection Managers folder present in the solution explorer and select the New Connection Manager option from the context menu.

Cache Connection Manager in SSIS 2

When you click on the New Connection Manager option, an Add SSIS Connection Manager window opened to select the connections managers from the list.

Cache Connection Manager in SSIS 3

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.

Cache Connection Manager in SSIS 4

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.

Cache Connection Manager in SSIS 5

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

Cache Connection Manager in SSIS 6

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

Cache Connection Manager in SSIS 7

Click on the open button to select the cache lookup.caw file.

Cache Connection Manager in SSIS 8

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.

Cache Connection Manager in SSIS 9

Click the ok button to finish configuring the Cache Connection Manager.

Cache Connection Manager in SSIS 12

From the above image, you can see we successfully created Cache Connection Manager in SSIS.

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy