azure-sql-databaseazure-data-factory

Azure SQL Insert Failure


Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table. Sort order incorrect for the following two rows: primary key of first row: (202101, 0xdef2170005000000), primary key of second row: (202102, 0xdef2170005000f00).

I keep getting the above problem on a weekly basis.

In short, we've got a DataFactory pipeline which is running a stored procedure which is inserting data into a fact table for a specific time period. The ADF pipeline runs this stored procedure within a for each loop, where the batch count is set to 10.

At the point the stored procedure does the insert, the fact table is a heap; it has no triggers, indexes, keys, or constraints.

So, can anyone explain why this is happening?


Solution

  • This was caused by a dodgy index on the table. Now the index has been corrected, the error no longer happens.