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

0
votes

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.

1
informaticainformatica-powercenter

1 Answers

1
votes

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.