ADF copy data to Dataverse / upsert can use primary key instead of alternate key?

Question

Using Azure Data Factory to copy from Azure SQL table to Dataverse.

Pipeline with copy data activity, with SQL as source and dataverse as sink datasets.

Dataverse always needs Upsert as Write behavior and, though the documentation says specifying an Alternate key is not Required (https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365?tabs=data-factory#dynamics-as-a-sink-type) - it is.

I cannot use the Contact table primary key contactid - I must define an alternate key in Dataverse. But I only have the primary key to use.

I do not want to create an alternate key in Dataverse, and then automation to set it to the contactid, just to get around this.

How can I use the copy data to a dataverse sink dataset and use the dataverse table primary key?

Thanks for any help

Answer 1

@Rob Dawson Welcome to Microsoft Q&A platform and thanks for posting your question.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Ask: Using Azure Data Factory to copy from Azure SQL table to Dataverse.

Pipeline with copy data activity, with SQL as source and dataverse as sink datasets.

Dataverse always needs Upsert as Write behavior and, though the documentation says specifying an Alternate key is not Required (https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365?tabs=data-factory#dynamics-as-a-sink-type) - it is.

I cannot use the Contact table primary key contactid - I must define an alternate key in Dataverse. But I only have the primary key to use.

I do not want to create an alternate key in Dataverse, and then automation to set it to the contactid, just to get around this.

How can I use the copy data to a dataverse sink dataset and use the dataverse table primary key?

Solution: source selected contactid, Sink to Dataverse dataset with Upsert as method and Alt key empty, Mapping config contactid to contactid plus other columns to go update. Works for update (as is my scenario where I have the already existing contactid).

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Solution: source selected contactid, Sink to Dataverse dataset with Upsert as method and Alt key empty, Mapping config contactid to contactid plus other columns to go update. Works for update (as is my scenario where I have the already existing contactid).