Dynamic Cache Update with Expression Results
The Lookup transformation can update the dynamic lookup cache values with the results of an expression.
For example, a product table target has a numeric column that contains an order count. Each time the Lookup transformation receives an order for the product, it updates the dynamic cache order_count with the results of the following expression:
order_count = order_count + 1
The Lookup transformation returns the order_count.
You can configure how the Integration Service handles the case where the expression evaluates to null.
Null Expression Values
The expression returns NULL if one of the values in the expression is null. However, you can configure an expression to return a non-null value.
If the expression refers to a lookup port, but the source data is new, the lookup port contains a default value. The default might be NULL. You can configure an IsNull expression to check for null values.
For example, the following expression checks if lookup_column is NULL:
iif (isnull(lookup_column), input_port, user_expression)
If the column is null, then return the input_port value. Otherwise return the value of the expression.
Expression Processing
The Integration Service can insert and update rows in the dynamic lookup cache based on the expression results. The expression results might vary based on whether the lookup port value is NULL and is included in the expression.
When you enable Insert Else Update, the Integration Service inserts a row with the expression result if the data is not in the cache. The lookup port value is NULL when data does not exist in the cache. If the expression refers to the lookup port value, the Integration Service substitutes the default port value in the expression. When you enable Insert Else Update and the data exists in the cache, the Integration Service updates the cache with the expression result.
When you enable Update Else Insert, the Integration Service updates the cache with the expression result if the data exists in the cache. When the data does not exist in the cache, the Integration Service inserts a row that contains the expression result. If the expression refers to a lookup port value, the Integration Service substitutes the default port value in the expression.
Configuring an Expression for Dynamic Cache Updates
You can configure an expression for a dynamic cache lookup update.
You must enable the Lookup transformation to perform dynamic lookups before you can create a conditional expression.
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. If applicable, configure the other advanced properties that apply to the dynamic lookup cache.
5. To create an expression, select the Columns tab on the Properties view.
6. Click the drop-down arrow in the Associated Port column for the lookup port that you want to update.
7. Select Associated Expression from the drop-down list, and click Enter.
The Expression Editor appears.
8. Define the expression.
You can select input ports, lookup ports, and functions for the expression. The expression return value must match the datatype of the lookup port.
9. Click Validate to verify that the expression is valid.
10. Click OK.