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.

Set the error threshold to one when you run a session. When you set the error threshold to one, the session fails when it encounters the first error. The Integration Service does not write the new cache files to disk. Instead, it restores the original cache files, if they exist. You must also restore the pre-session target table to the target database.

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

input/output

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.

Set the Treat Source Rows As property to Data Driven in the session properties.

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.

Select these options in the Transformations View on the Mapping tab in the session properties.