Use a Router transformation to pass rows to the cached target when the NewLookupRow value equals one or two.

Use the Router transformation to drop rows when the NewLookupRow value equals zero. Or, output the rows to a different target.

Use Update Strategy transformations after the Lookup transformation to flag rows for insert or update into the target.

Verify that the Lookup transformation outputs the same values to the target that the Integration Service writes to the lookup cache. When you choose to output new values on update, only connect lookup/output ports to the target table instead of

output

ports. When you choose to output old values on update, add an Expression transformation after the Lookup transformation and before the Router transformation. Add output ports in the Expression transformation for each port in the target table and create expressions to ensure you do not output null input values to the target.

Select Insert and Update as Update when you define the update strategy target table options. This ensures that the Integration Service updates rows marked for update and inserts rows marked for insert.