How to perform UPSERT with duplicate records captured in different file in informatica

Question

I have below requirement-

• Source: Oracle
• Target: SQL server
• Load type: UPSERT
• Primary key on target column: (id+deptid+ctid)
• Target table columns
  • Name
  • ID
  • deptID
  • CtID
  • Place
  • quant
  • total_buy

I have to load the data into table and perform update else insert. I also need to perform below for error reporting:

• On Key violation, please skip that record and continue on loading the records. The skipped records save it in File (ct_err_.txt and save it on archive directory for 30 days. Data in error file is (id, deptid, ctid).

I have tried:

SQ -flow1 -> aggregation -> err file
   -flow2 -> sorter-lookup on target -> router-update else insert.

But it i not working properly.

Can you please help me here with the logic.

Answer 1

You don't need the first flow. All you need is the flow2 and on your Target in Session -> Mapping tab set the Reject file directory and Reject filename properties accordingly.

Informatica automatically does fulfill your requirement by redirecting rejected rows to a file of your choice. You may need to adjust the Stop on errors property in Config Object tab of the session.