Advanced Lookup Cache

Advanced Lookup Cache

Parent Previous Next

Advanced Lookup Cache Connection Manager

The "Advanced Lookup Cache Connection Manager" is used to setup the cache database as well as configure how data will be retrieved from it. Used with Advanced Lookup Cache Transform and Advanced Lookup Transform.

Available Columns

The available columns tab allows you to define columns that will be part of the cache, whether or not the column will be output when a lookup is performed and what type of cache database to use.

  Use In-Memory Cache - This tells the connection manager whether to use in-memory cache or on-disk cache when creating the cache database.

  Define Customer Cache Location - By default, the cache connection manager is created in a temporary file location defined by SSIS. You can choose to store the database in a custom location by checking this box and entering/selecting a path to the file. The file should be defined with an .s3db extension.

 Output Columns - The output column tells the connection manager to return this value when a lookup is performed. The output columns can be seen on the "Advanced Lookup Transform UI" after a cache connection manager is selected.

Input Parameters

  Input Parameter  - Used by the advanced lookup transform to define the lookup values to find rows in the cache database. They are mapped from Input columns in the Advanced Lookup Transform.

  Input Parameter Condition - This is where you can easily create conditions for queries. By clicking the word 'And', you can select between an And' and Or conditions. Clicking the green '+' will add more conditions and the red 'x' removes them.

Important Note

The first condition requirement is the table key is equal to the input parameter.

Click the green 'New Group Icon' at the end of the condition to create a new And/Or group.


 Multiple Row Match Policy - This option lets you define what should happen if multiple rows are found during a lookup

 Make All Comparisons Case Sensitive - Using this option will make all string comparisons performed case sensitive (e.g. PragmaticWorks will not equal pragmaticworks)

Setting up the Connection Manager

Setting up Parameters and Conditions