Azure Data Factory with Dataverse Sink (mapping data flow) Upsert broken?

Question

Simple ADF data flow with a dataverse sink (Dynamics 365).

Created an alternate key on a custom dataverse table and attempting to do an Upsert.

I have the Alter row transform with "Upsert if" -> true()

I have set the sink to Upsert (nothing else checked) and selected the key from Dataverse for the "Alternate key name" option.

When I attempt to run the Dataflow I get the error message: "Job failed due to reason: Insert method is not defined and you are executing insert operation. Please select insert method!"

I have not selected Insert explicitly on the sink, does this mean that Upsert is attempting to insert based on no matching record? If so, how do i 'select insert method'?

Answer 1

@Noah Gilbert

Thanks for reaching out to Microsoft Q&A

Based on your description, it's likely that the Upsert operation is indeed attempting to insert a record because no matching record is found in the Dataverse table. This is because you have set the "Upsert if" condition to true() in the Alter row transform, which means that if no matching record is found, a new record will be inserted.

To resolve this issue, you need to define the insert method for the Dataverse sink. Here are the steps to follow:

1. Go to the Sink settings: In your ADF data flow, navigate to the Sink settings for the Dataverse sink.
2. Select the Insert method: In the Sink settings, select the "Insert method" option and choose the appropriate method for inserting records into your Dataverse table.
3. Save and re-run the data flow: Save your changes and re-run the data flow to test the Upsert operation.

If you continue to face issues, it might be helpful to check the following:

• Alternate Key Configuration: Ensure that the alternate key you have configured in Dataverse is correctly set up and matches the key used in your Data Flow.
• Data Flow Debugging: Enable Data Flow debug mode to get more detailed error messages and insights into what might be going wrong.

For more detailed guidance, you can refer to the official documentation on Sink transformation in mapping data flow1 and Alter row transformation.