Dynamics 365 Data Synchronization with Azure Data Factory

2020-02-29

All articles from this blog can ONLY be redistributed on an Attribution-NonCommercial-NoDerivs basis. Please credit the source, thank you.
Twitter:@kelvinshen
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:

  • Master Data Managment (MDM) Systems
  • Event Driven with Azure Service Bus
  • ETL with standard ADF
  • ETL with ADF v2 and SQL Server Integration Service (SSIS) packages
  • Data Export Service - I think this one is falling out favour as recommended by Microsoft
  • CDS Data Integrator

Key Considerations

  • There are quite a few PowerPlatform features and services don’t work in the China tenants.
  • Data integration vs Data Migration (data volume)
  • Data integration vs Data synchronization (data tranformation complexity)
  • On premise or in cloud or a mixture
  • Data sources and if they have supported data connectors

Azure Data Factory (Standard)

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

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.

Query

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

@concat('<fetch>
  <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'), '" />
    </filter>
  </entity>
</fetch>')

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