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.
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 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.
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