Conditional Dynamic Lookup Cache Updates
You can update a dynamic lookup cache based on the results of a boolean expression. The Integration Service updates the cache when the expression is true.
For example, you might have a product number, quantity on hand, and a timestamp column in a target table. You need to update the quantity on hand with the latest source values. You can update the quantity on hand when the source data has a timestamp greater than the timestamp in the dynamic cache. Create an expression in the Lookup transformation similar to the following expression:
lookup_timestamp < input_timestamp
The expression can include the lookup and the input ports. You can access built-in, mapping, and parameter variables. You can include user-defined functions and refer to unconnected transformations.
The expression returns true, false, or NULL. If the result of the expression is NULL, the expression is false. The Integration Service does not update the cache. You can add a check for NULL values in the expression if you need to change the expression result to true. The default expression value is true.
Conditional Dynamic Lookup Cache Processing
You can create a condition that determines if the Integration Service updates the dynamic lookup cache. If the condition is false or NULL, the Integration Service does not update the dynamic lookup cache.
If the condition is false or NULL, regardless of the Lookup transformation properties, the NewLookupRow value is 0 and the Integration Services does not update the dynamic lookup cache with any row inserts or updates.
If the row exists in the cache and you enable Insert Else Update or Update Else Insert, the NewLookupRow value is 1 and Integration Service updates the new row in the cache.
If the row does not exist in the cache and you enable Insert Else Update or Update Else Insert, the NewLookupRow value is 2 and Integration Service inserts the new row in the cache.
Configuring a Conditional Dynamic Lookup Cache
You can configure an expression that determines whether the Integration Service updates a dynamic lookup cache.
1. Create the Lookup transformation.
2. On the Run-time tab of the Properties view, select Lookup Caching Enabled.
3. On the Advanced tab of the Properties view, select Dynamic Lookup Cache.
4. To enter the condition, click the down arrow for the Update Dynamic Cache Condition property.
The Expression Editor appears.
5. Define an expression condition.
You can select input ports, lookup ports, and functions for the expression.
6. Click Validate to verify that the expression is valid.
7. Click OK.
8. If applicable, configure the other advanced properties that apply to the dynamic lookup cache.