azure data factory view code and stop direct path loads to oracle

Question

Hi,

We are using pipelines to copy data from oracle to azure.

We accidentally set the sink (target) last week to a table on oracle and it inserted data into a table which had a primary/unique key constraint on it and thus made the index unusable.

It looks like azure has used a direct path load to write data to data blocks rather than using a conventional path load which would have failed due to the presence of the unique key constraint.

I have a few questions

1) How can we see the exact sql azure uses when writes to an oracle or azure sl database? Azure data factory graphical tool but woudl like to see the exact sql it generates as belive it will have have used an insert with hints on it when loaded o oracle.

2) How can we disable the direct path load the copy activity seems to use when using copy activity to copy data to oracle target.

3) Does sql server/azure slq server database have the concept of direct path load whereby can write to data blocks directly thus bypassing primary key constraints on tables?
Concern is if someone runs a copy activity to a table when sink has target it doesn't obey the unique key constraint.

Many Thanks

Answer 1

Hi @Philip O'Rourke ,

Thank you posting query in Microsoft Q&A Platform.

Usually, When we have primary key or Unique key constrains we should see error while loading duplicate values. But in your case, you are saying its not happening with oracle. I am checking with internal team more on this and get back with updates.

There is no way to see SQL getting used behind the scenes of copy activity and I don't think we can enable or disable some behavior if there is no such option available inside Copy activity GUI.

SQL server don't have Direct path load for loading bulk data. Instead in SQL server Bulk Copy Program (BCP) for loading bulk data.