Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table.

[azure-samples]

Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table.

[gavindew]

Ok, so the issue seems to be caused by this:
public string GetOrderBy()
{
return SourceTableInfo.PrimaryIndex.GetOrderBy(excludePartitionColumn:true);
}

This means the ORDER BY generated in the SELECT statement excludes the partitioning key, but the ORDER BY added to the BulkCopy itself includes the partitioning key. When I change excludePartitionColumn to false, it works fine.

[yorek]

Good catch, thanks! Not sure I understand what are the conditions that make this issue happen. You have the table on source and the destination created with the same partitioning function and scheme. Both have an Clustered Index.
Adding the Partition column to the order by is needed to make sure sorting does not happen on the destination.
Are you able to create a repro so that I can take a look at it?
Thanks!

[gavin-eq]

This script should highlight the issue: By excluding the partition column, the sort order of the data will be different on select and in bulk copy object.

CREATE PARTITION FUNCTION pfTest AS RANGE LEFT FOR VALUES (N'2018-12-31T00:00:00.000', N'2019-12-31T00:00:00.000')
GO
CREATE PARTITION SCHEME [psTest] AS PARTITION [pfTest] TO ([PRIMARY], [PRIMARY], [PRIMARY])
GO
CREATE TABLE PartitionTest (
PartitionDate date not null,
TransactionId int not null,
CategoryId int not null
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
PartitionDate ASC,
TransactionId ASC,
CategoryId ASC
) ON psTest
)
GO
INSERT INTO PartitionTest (PartitionDate, TransactionId, CategoryId)
VALUES
('2018-01-01', 2, 1),
('2018-01-02', 1, 1)

[yorek]

Thanks, will take a look ASAP

[yorek]

Thanks, I was able to repro the problem and fixed it. Added also unit tests and CI/CD pipeline to make sure not to introduce regression bugs.
Fixed here: b85f6b8