• Here you are using Lookup Cascading Pattern. Please make sure the following:

    1. First Lookup -- Setup the cache property to FULL Cache. This will have 2 outputs for you. 1 All exact match records and second rejected records.

    All Exact match records direct them to UNION ALL task.

    2. For Rejected records in Setp1 -- map it to second look up and set its properties to partial Cache.

    All matches redirect them to UNION All Task.

    3. Rejected records in Step 2 -- Map it to NO Cache lookup

    All matches redirect them to UNION All task

    Rejected records created a surrogate key in the lookup and then update the record and redirect output to UNION all.

    It would be nice if you could give a image of your current SSIS package for further inputs.