CRM Data Migration using Azure Data Factory

Question

Am trying to migrate data from CRM online to Data Lake.

within Copy Activity of ADF, am using FetchXML to read data from CRM (CDM)

FetchXML:

<fetch>
  <entity name="cdm_location">
    <attribute name="cdm_gisid" />
    <attribute name="cdm_locationid" />
    <attribute name="cdm_locationtype" />
    <attribute name="cdm_name" />
  </entity>
</fetch>

Issue is ADF looks and the Sample dataset of above source query and determines the columns to be extracted. In my case column cdm_gisid within the sample dataset is NULL and hence this column is being ignored by ADF.

Is there a way in FetchXML saying if column is null replace with 'Default Value' so that ADF can see the column in the Sample Dataset.

[Or] is there better way in ADF to pull data from CDM, bringing every column irrespective if its null or not null.

Answer 1

You just need to use a column map which can be defined in the Mapping tab of your copy activity. This is a known issue and is recommend in the ADF/D365 documentation.

See the Important note in the following link. https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365#dynamics-as-a-source-type