Share via

I have a case where we need to update some records into Oracke RDS in AWS using ADF

Samir Kohli 0 Reputation points
2025-03-04T19:31:18.22+00:00

Hi MS Team,

I am working on a project where we need to perform Update from Synapse into Oracle RDS using ADF.

I have ODBC linked service which I have been using to perform inserts, but no option is available to Update in the ADF pipeline.

Has anyone else had a similar issue?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,323 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Chandra Boorla 9,685 Reputation points Microsoft External Staff
    2025-03-05T01:09:09.39+00:00

    Hi @Samir Kohli

    Thank you for posting your query!

    Azure Data Factory (ADF) does not provide a direct Update option when using an ODBC Linked Service in a Copy Activity. However, you can update records in Oracle RDS using the following approaches:

    Use a Stored Procedure Activity (Recommended) - If you have a stored procedure in Oracle RDS that handles updates, call it using the Stored Procedure Activity in ADF. This is the most efficient method for bulk updates, as it avoids row-by-row processing.

    Use a Staging Table and MERGE Statement (Best for Bulk Updates) - Copy the data into a staging table in Oracle RDS using a Copy Activity. Then, use a MERGE SQL statement inside a stored procedure to update the target table efficiently. This approach minimizes performance overhead and ensures consistency.

    ForEach Activity for Row-by-Row Updates (Not Recommended for Large Datasets) - If batch processing is not an option, you can: Use a Lookup Activity to fetch records that need updating. Use a ForEach Activity to iterate through each record and execute an update via a Stored Procedure Activity or Script Activity. This method performs poorly for large datasets and should be used only as a last resort.

    Use Script Activity for Direct SQL Execution - If your ODBC connection allows executing raw SQL, you can use the Script Activity to run an UPDATE statement directly on Oracle RDS.

    Additional Considerations:

    • ODBC Driver Support - Ensure your ODBC driver allows UPDATE statements (some are read-only).
    • Permissions - The database user needs sufficient privileges to perform updates.
    • Error Handling - Use Try-Catch blocks in ADF to log and manage failures.

    Final Recommendation - If updating a large number of records, the Stored Procedure + MERGE method is the most efficient. For small updates, Script Activity can be an alternative. Avoid row-by-row updates unless absolutely necessary.

    For additional information, please refer the following Microsoft documentation:

    I hope this information helps. Please do let us know if you have any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.