Share via

Using CRM as a source in the ADF Metadata-driven copy task results in Fetch XML syntax errors

Warren Briggs 20 Reputation points
2024-05-20T06:54:23.0266667+00:00

I am using the Metadata-driven copy task in Azure Data Factory (ADF). The process works as expected when using SQL server as a source, but when I use CRM as a source, I get source query syntax errors when the pipeline executes.

The problem seems to be related to the Fetch XML query that is created for the source. The copy task wizard creates the normal pipelines (TOP LEVEL, MIDDLE LEVEL, BOTTOM LEVEL) and SQL scripts for the control table and the stored procedure. But after the initial creation, which appears to go normally, I get two errors:

Pipeline execution. The pipeline fails when the GetMaxWatermarkValue activity in the Bottom Level pipeline is run. The error is:

Failure happened on 'Source' side. ErrorCode=DynamicsOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Dynamics operation failed with error code: DynamicsOperationFailed, error message: The Fetch Xml query specified is invalid..,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,''Type=System.Xml.XmlException,Message=Data at the root level is invalid. Line 1, position 1.,Source=System.Xml,'

Edit Control Table. If I try and edit the control table, the configuration data does not fully load, and the list of source tables is not populated in the wizard.

I thought there might be an issue with the for the initial creation of the control table that was generated by the copy tool wizard, so it is attached. Dataverse Source - Control Table.txt. In this example I only have 1 source table that I'm trying to do a delta load based on the "modifiedon" field as the watermark value.

I've tried multiple CRM connectors (Dynamics 365, Dynamics CRM and Dataverse) and the same issues happen with all three. The metadata-driven copy task does not appear to like using Fetch XML (i.e. CRM) in the source.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,806 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaikMaheer-MSFT 38,291 Reputation points Microsoft Employee
    2024-05-20T10:15:35.94+00:00

    Hi Warren Briggs,

    Thank you for posting query in Microsoft Q&A Platform.

    It seems that the issue you are facing is related to the Fetch XML query that is being used as the source in the Metadata-driven copy task in Azure Data Factory (ADF). The error message you are seeing indicates that the Fetch XML query specified is invalid.

    One possible reason for this error could be that the Fetch XML query is not properly formatted or contains invalid characters. You may want to review the Fetch XML query that is being used as the source and ensure that it is valid and properly formatted.

    Another possible reason for this error could be related to the authentication method being used to connect to the CRM source. You may want to ensure that the authentication method being used is supported by the Metadata-driven copy task in ADF and that the necessary permissions are in place to access the CRM source.

    Additionally, you may want to try using a different connector or driver to connect to the CRM source, as different connectors may have different requirements and limitations.

    If none of these solutions work, you may want to consider using a different approach to extract data from the CRM source, such as using a custom script or a third-party tool to extract the data and then load it into Azure Blob Storage or Azure Data Lake Storage, which can then be used as the source in the Metadata-driven copy task in ADF.

    I hope this helps! Let me know if you have any further questions.