Dynamics 365 Data Synchronization with Azure Data Factory


All articles from this blog can ONLY be redistributed on an Attribution-NonCommercial-NoDerivs basis. Please credit the source, thank you.
Blog:Kelvin Shen's Blog

People’s Republic of China government rolled out a cyber security law in July 2017. It forced my client to localize their data storage for their Chinese residents personal data. Since the company’s headquarter is still in Europe, data synchronization between global and China IT systems became urgent.

There are hundred ways to (hypothesically) skin a cat. Below is a list of implementation options:

Key Considerations

  • There are quite a few Power Platform features not available in the China tenants.
  • Advanced quering
  • Supported data types
  • Data integration vs data migration (data volume)
  • Data integration vs data synchronization (data tranformation complexity)
  • Are data source and distination on-premise or in cloud or a mixture
  • Data sources and if they have supported data connectors
  • Multi-tenant support

Azure Data Factory (Standard)

I am glad to report that this actually works but with some significant limitations.


ADF Flow Activity

ADF Data Flow Activity doesn’t support the OOTB Dynamics 365 connector. If you want to use the OOTB Dynamics 365 connector, you will have to use the ADF ‘Copy Data’ Activity. The bad news is that ADF ‘Copy Data’ Activity has very limited data transformation support.

So, it a kind of forcing you to have a stage database and do the data transformation there. That’s something extra to maintain.


ADF D365 connector does support FetchXml which is awesome. However, the maximum length of your FetchXML query is 8192 characters!

A Sample FetchXML Query with Dynamic Values

  <entity name="account" >
    <attribute name="primarycontactid" />
    <attribute name="parentaccountid" />
    <attribute name="name" />
    <filter type="and" >
      <condition attribute="name" operator="begins-with" value="Contoso" />
      <condition attribute="modifiedon" operator="le" value="', formatDateTime(pipeline().TriggerTime, 'o'), '" />

Data Source Schema

ADF ‘Copy Data’ Activity will automatically generate source data schema based on the FIRST ROW of the dataset. So, if you notice missing source fields in your data map, you probably want to check any NULL values in the first row.

Data Types

Azure Data Factory v2 + SSIS

With ADF v2 Integration Runtime (IR), your existing SSIS package investment should continue to work. If you are familiar with KingswaySoft, they also have a version which support ADF v2 IR.

Leave a Reply

comments powered by Disqus

Table of Content