Azure Data Factory Insert error on Dataverse sink

Question

Hi,

I am trying to write data to Dataverse sink(contact entity) using Azure Data factory copy activity. Its giving error as Missing key col for ContactID. This field is guid and should be autogenerated. It seems its because of the 'Upsert' write behaviour. Can we change this so that records could get inserted without the check on ContactID field and it gets generate automatically as expected.

ErrorCode=DynamicsMissingKeyColumns,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Input DataSet must contain keycolumn(s) in Upsert/Update scenario. Missing key column(s): contactid,

Please advise.

Thanks

Answer 1

Hello @Pankaj Singh !

Okay got it it is the whole Setup

since you want to perform a pure Insert operation where the ContactID (GUID) should be autogenerated by Dataverse, you need to change the approach to avoid the Upsert behavior.

To remove key columns from mapping:

• Navigate to the Mapping tab in the Copy Data activity.
• Make sure the ContactID column, or any other key column, is not mapped. If it is, ADF will require a value for this column, which can lead to the error you've encountered.
• By excluding the ContactID from mapping, Dataverse will generate a new GUID for each record automatically.
• Set Write Behavior to Insert:
• While ADF's UI doesn't explicitly offer an "Insert" option like it does for other sinks, you can ensure an insert operation by not mapping any key columns and not setting the dataset to perform an Upsert.
• Not mapping key columns indicates that the operation should be an insert.
• Optionally pre-populate data in Dataverse:
• Should you choose to assign GUIDs yourself or prevent conflicts, consider pre-generating GUIDs in your data source. If you prefer automatic GUID generation by Dataverse, make sure the ContactID is neither in the source data nor mapped in the copy activity.

--

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 2

Hello @Pankaj Singh !

Welcome to Microsoft QnA!

The "Missing key column for ContactID" error often occurs when using the "Upsert" write behavior in ADF's Dataverse sink.

To remove the Upsert Behavior:

1. In your ADF copy activity, navigate to the Sink tab.
2. Change the Write behavior from "Upsert" to "Insert," which instructs ADF to insert new records without considering the ContactID field, allowing Dataverse to auto-generate the GUID.

If Upsert is necessary, include a dummy ContactID in your source:

1. Generate GUIDs by adding a new column in your source dataset for the ContactID and create unique GUIDs for each record using various available tools and libraries.
2. In the mapping of your ADF copy activity, ensure this new GUID column corresponds to the ContactID field in the Dataverse sink.

Note: When updating existing records, the GUIDs must match the current ContactID values in Dataverse to prevent the creation of duplicate records.

Alternatively, consider using a different key in Dataverse, such as an email address, for upsert operations. Ensure this alternate key is included in your source data and mapped accurately.

--

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 3

Hello @Pankaj Singh !

Thanks for the clarification

Since his is the case you have to go to Mappings and make the config there '

1. Configure Source (Salesforce):
   • In the Copy Data activity, select Salesforce as your source.
   • Choose only the columns that you want to migrate from Salesforce to Dataverse.
2. Configure Sink (Dataverse):
   • In the Sink settings, select the Dataverse entity (e.g., Contact entity) as your destination.
   • Ensure that you do not map the ContactID column or any other auto-generated fields in Dataverse. By doing this, Dataverse will automatically generate a ContactID for each new record.
3. Column Mapping:
   • Go to the Mapping tab in your Copy Data activity.
   • Map only the columns from Salesforce that you want to insert into Dataverse. For example, if you only want to insert FirstName, LastName, and Email, map these fields to the corresponding fields in the Dataverse Contact entity.
   • Leave out any columns that should remain blank or should not be inserted. This includes the ContactID.
4. Disable Key Columns in Mapping:
   • Since you don't want to perform an Upsert operation, ensure that no key columns (such as ContactID) are mapped or required. This will ensure that the operation behaves as an Insert and not an Upsert.

I know that a lot of Columns means a lot of hand work but ..this is the case since you wan to make the mappings specific. I used to have a Python Script to do that but it was for Postgres Sink

--

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards