When you enable lookup caching, a

mapping

task builds the lookup cache when it processes the first lookup request. The cache can be static or dynamic. If the cache is static, the data in the lookup cache doesn't change as the mapping task runs. If the task uses the cache multiple times, the task uses the same data. If the cache is dynamic, the task updates the cache based on the actions in the task, so if the task uses the lookup multiple times, downstream transformations can use updated data.

You can use a dynamic cache with most types of lookup sources. You cannot use a dynamic cache with flat file or Salesforce lookups. For more information about using a dynamic cache with a specific type of lookup source, see the help for the appropriate connector.

Based on the results of the lookup query, the row type, and the Lookup transformation properties, the

mapping

task performs one of the following actions on the dynamic lookup cache when it reads a row from the source:

Inserts the row into the cache

The

mapping

task inserts the row when the row is not in the cache. The

mapping

task flags the row as insert.

Updates the row in the cache

The

mapping

task updates the row when the row exists in the cache. The

mapping

task updates the row in the cache based on the input fields. The

mapping

task flags the row as an update row.

Makes no change to the cache

The

mapping

task makes no change when the row is in the cache and nothing changes. The

mapping

task flags the row as unchanged.

The dynamic Lookup transformation includes the return field, NewLookupRow, which describes the changes the task makes to each row in the cache. Based on the value of the NewLookupRow, you can also configure a Router or Filter transformation with the dynamic Lookup transformation to route insert or update rows to the target table. You can route unchanged rows to another target table or flat file, or you can drop them.

You cannot use a parameterized source, target, or lookup with a Lookup transformation that uses a dynamic cache.