Search code examples
azure-data-factory

How to construct a FETCH query to Fetch all fields in Dynamic CRM table with Azure Data Factory


I would like help fetching a table in Dynamics CRM with Azure Data Factory

I use the following parameters to select all fields from a regular table

@concat('SELECT * FROM ',pipeline().parameters.Domain,'.',pipeline().parameters.TableName)

When contructing a query to select all fields from a table in Dynamics CRM there is are requirement to use FETCH method, see image

enter image description here

I would like help converting the parameters

@concat('SELECT * FROM ',pipeline().parameters.Domain,'.',pipeline().parameters.TableName)

To work with FETCH as shown in the image.

My original thoughts were I needed to do the following:

@concat('FETCH * FROM ',pipeline().parameters.Domain,'.',pipeline().parameters.TableName)

But that didn't work.

The error I'm getting is:

{
    "errorCode": "2200",
    "message": "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,'",
    "failureType": "UserError",
    "target": "Copy From CRM to SQLDB",
    "details": []
}

Any thoughts?


Solution

    • The error The Fetch Xml query specified is invalid indicates that the fetch query is invalid.

    • As given in the query (sample of fetch), the query has to be built. Hence try using the following dynamic content instead in the query section:

    <fetch>
    <entity name="@{pipeline().parameters.domain}.@{pipeline().parameters.table}">
    <all-attributes/>
    </entity>
    </fetch>
    
    • The above would generate a fetch query in required format. I have used set variable activity to demonstrate how the query would be built.

    enter image description here