Cant choose Key for upsert, in Copy Data-activity

Question

I want to copy Data from an Azure SQL to a comsosdb container, with the copy data activity of Azure Synapse. As I cannot enter the uniqe key column, for the upsert behaviour, i receive a unique key violation error.

Why is there no option to select the key-column when copying into comosdb

Answer 1

Hello Axel,

Welcome to the Microsoft Q&A forum.

Azure Synapse's Copy Data activity does not provide an option to specify the unique key column for upsert operations into Azure Cosmos DB.

The reason behind this is:

The id field in Cosmos DB item acts as the key column to perform update or delete or upsert operations. When performing update or delete actions, specifying the partition key is mandatory. This is because the service uses the partition key to search for the id field value and perform the update or delete operation accordingly.

The unique key violation error you're encountering suggests that the data being copied contains duplicate values for the id field defined in your Cosmos DB container.

Please ensure that the data in Azure SQL does not contain duplicates for the fields that form the unique key in Cosmos DB.

I hope this answers your question.

Please let me know if you have any further questions.