none
Azure Data Factory - Copy to Oracle - bulk?

    Question

  • Hi,

    I have a pipeline that copies data from SQL Server to Oracle. It appears to be doing a bulk-copy under the hood, since it ignores primary key constraints on the Oracle side. Is there a way to turn this off?

    thanks

    Wednesday, October 17, 2018 9:15 PM

All replies

  • Hello,

    May I know more information about "it ignores primary key constraints on the Oracle side"?  Bulk-insert should honor primary key constraints.  What error message did you see from Oracle side?

    Thanks. 

    Thursday, October 18, 2018 3:07 AM
  • Hi,

    It should not happen, copy data copy entire data set considered for data movement unless you ignored some of the columns intentionally. Can you please let us know a little more about it.


    Cheers,

    Thursday, October 18, 2018 5:37 AM
  • Hi, I'm not ignoring any columns.

    The first time I run it, there is no error on the ADF side.

    It enters the data with duplicate rows. After that I will get the oracle error.

    ORA-01502: index or partition of such index is in usable state problem

    It's like it destroys the primary key constraint.

    The only thing I'm doing different is that I'm not specify the exact mappings in the COPY action. But, the source SQL table and destination Oracle table have the same schema.


    Thursday, October 18, 2018 4:59 PM
  • So, we performed the action again, this time we turned on the monitoring on the Oracle side to see how the query came across, and this is what we saw.

    INSERT /*+ SYS_DL_CURSOR */ INTO "DTA"."F5847012" ("SZEDTY","SZEDSQ","SZEKCO","SZEDOC","SZEDCT",.... ,"SZURAT","SZURAB","SZURRF","SZTORG","SZUSER"

    It's using the SYS_DL_CURSOR statement, which is a Direct Path Load, and that bypasses constraints.

    There's a hint that could be used, EVALUATE CHECK_CONSTRAINTS, but I don't know how to modify that in Azure Data Factory.

    Any ideas?

    thanks

    Wednesday, October 24, 2018 5:16 PM