Unique Constraint error

Scenario:We have  insert else update mapping and we have look up transformation for target.When we are loading data to target  data should insert or it should update.If this is the case why we get unique Constraint error.

Ans:If we are getting unique Constraint error the look up transformation  we are using should be static.why because,satic look up will keep the copy of target table only when the session starts.

So lets take this case, a row having data "john" for name field is loading to the target.While going through look up transformation data will be checked against target.

For this scenario lets think like we don't have this data in target and an index is assigned on the name field on target. Lookup condition for look up here is 'Name=In_Name' .when this row passes through look up, it will be checked against the condition.If the condition fails we will mark it for insert else for update.

For this scenario, look up condition fails. Now  data got inserted into target and the target table is changed but the cache of the look up transformation do not got updated.

What happens if the same data  i.e duplicate comes from the source again.Again it is checked against the LookUp condition('Name=In_Name') and the condition fails and the row will be marked for insert.

As the unique index is defined on the name field of target this row is rejected and unique constraint error is shown.