Azure Data Factory: Fetch xml to return all the attributes not working properyl

Question

Hi,
Thanks in advance for your response.

I am using copy activity to copy the data from one environment to another environment in dynamics 365.
If I dint use any filter in the fetch xml query, it works fine. Otherwise i am getting missing key fields error.

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

Fetch XML used in dataset query
ex. <fetch>

<entity name='contact'>

<all-attributes/>

<order attribute='createdon' descending='false' />

<filter type='and'>

<condition attribute='createdon' operator='on-or-after' value='2021-05-27' />

<condition attribute='createdon' operator='on-or-before' value='2024-05-27' />

</filter>

</entity>

</fetch>

Mapping: Both source and target schema are same, so am using auto mapping feature.

Answer 1

Hi Maniraj K V

Welcome to Microsoft Q&A platform and thanks for posting your question here.

The scenario you’re dealing with involves using the copy activity in Azure Data Factory for data migration between two Dynamics 365 environments. When no filter is applied in your FetchXML query, the process works fine. However, when you introduce a filter based on the createdon attribute, you encounter an error stating that a key column, contactid, is missing from the input dataset, which is necessary for the Upsert/Update scenario.

The FetchXML query you’ve provided is intended to retrieve all attributes for the contact entity within a specified date range. The error occurs because the contactid, despite being a key column, is not included in the dataset when the filter is applied. This key column is essential for identifying records in the target environment for update or upsert actions.

To resolve this error, you should explicitly include the contactid in your FetchXML query.

Here’s a revised version of your FetchXML that includes the contactid:

XML

<fetch>
  <entity name='contact'>
    <attribute name='contactid' />
    <all-attributes/>
    <order attribute='createdon' descending='false' />
    <filter type='and'>
      <condition attribute='createdon' operator='on-or-after' value='2021-05-27' />
      <condition attribute='createdon' operator='on-or-before' value='2024-05-27' />
    </filter>
  </entity>
</fetch>

By ensuring the contactid is included, the copy activity should be able to process the Upsert/Update operation without encountering the missing key columns error.

Reference:
https://learn.microsoft.com/en-us/azure/data-factory/connector-troubleshoot-dynamics-dataverse#error-code-dynamicsmissingkeycolumns

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.