how to execute Oracle merge statement in ADF v copy data ?

Copy data activity expects a mapping between source and sink to insert record from source to target.

In real time, especially for scd type 1 data load, you may require insert, update and delete statement execution.

Adf doesn't support scd type 1 for Oracle db source.

Resolution:

Oracle supports the above through merge statement.

For implementing above in copy data, I have done the trailing:

1. The select SQL statement used in merge clause be used as source SQL. In filter ad 1=2

2. The update sink connection with target table in merge clause

3. In sink section, execute merge clause statement in pre-copy script section

4. Debug for insert , update and delete use cases.

Let me know if you face any challenges.

P.S: we don't need to call procedures to achieve the above