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
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?
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>