***Update:*** Microsoft have identified the problem and will be fixing it!

I am attempting to use Azure Data Factory to load a parent and child table in Azure SQL, which is enforced in the database by a foreign key.

My DataFlow is very simple, reading from staging tables and writing 1-for-1 into the destination tables. One of the reads has an exists constraint against a third table to ensure that only the correct subset of records are loaded.

I have two very similar DataFlows loading two kinds of record with similar parent-child relationships, one of them works just fine, the other fails with a foreign key violation. Sometimes. It's not consistent, and changing seemingly unrelated things such as refreshing a Dataset schema sometimes makes it work.

Both DataFlows have Custom Sink Ordering set to make the parent table insert happen first at Order 1, and the child record happen at Order 2.

Am I using this feature correctly, is this something that Custom Sink Ordering should give me?

This is the job layout, it's actually loading two child tables:
[![Azure Data Factory flow loading a parent table and two child tables](https://i.stack.imgur.com/zbzTU.png)](https://i.stack.imgur.com/zbzTU.png)

I tried removing the top sink, so it only loads the Write Order 1 table (sinkSSUSpatialUnit) and the Write Order 2 table (sinkSSUCompartment) that is failing with a foreign key violation, and the problem does not happen in that cut-down clone of the DataFlow.

Microsoft have found a problem with Custom Sink Order not working as intended intermittently, and will be fixing it. I will update this if I find out any more.