Sink Limitations with the Dynamics 365 Customer Engagement/Common Data Service Connector for Azure Data Factory

Regular readers of the blog may have noticed that the past couple of posts has been very Azure Data Factory V2 (ADF) focused, particularly in the context of Dynamics 365 Customer Engagement (D365CE) and the Common Data Service (CDS). I’ve provided an on overview of the different connectors available today for both of these applications and also discussed some of the hurdles you may find when it comes to mapping data across to your desired fields within D365CE/CDS. Today’s post naturally follows on from these subjects, as we aim to take a look at some of the mapping limitations currently in-place when importing data using the D365CE/CDS connectors.

As highlighted on Microsoft’s official article on the subject, the vast majority of field types available within D365CE/CDS can be mapped to a destination (or “sink” location). These, for the most part, involve the standard data types that .NET developers will be familiar working with and if, for example, you want to map data from a SQL Server data source into D365CE/CDS, you should have little difficulty implementing these mappings. However, two important attribute types within D365CE/CDS are not supported for mapping, under any circumstance. And, although they may not cause you difficulties in all but the most complex integration scenarios, if you are working with out of the box system entities using pre-built relationships or are wanting to perform record assignment changes within your ADF pipelines, you may find yourself hitting a brick wall. The next two sections look at these attribute types in greater detail, as well as offering a suggested route to work with them alongside ADF.

AttributeType.Customer

This attribute type is more commonly known as the Customer lookup field, a special kind of relationship that allows a user to associate either a Contact or an Account record with another Entity record. This data type has traditionally been used extensively by various system entities within the application and, more recently, has been exposed out for system customizers to create as well. Further details about this special data type can be found on the Microsoft Docs website. Given its somewhat strange behaviour, from an entity-relationship standpoint at least, we can guess as to why this data type is not supported within ADF; perhaps because we have no way of telling CRM, as part of a copy activity, whether a supplied entity name or GUID belongs to a specific entity type. However, this limitation may cause some frustration, particularly if you are performing a migration from an on-premise Dynamics CRM instance to online, and you wish to keep in place any existing relationships from your source system.

If you are completely set on using this field as intended and to import data into it (which makes sense, given that is relied upon for many out of the box components within D365CE), then a possible workaround can be implemented with a mixture of D365CE/CDS entity customisations and a Microsoft Flow to perform most of the heavy lifting:

First, create two temporary lookup fields on the entity that you wish to import Customer lookup data into it, to both the Account and Contact entities respectively:
Within your ADF pipeline flow, you will then need to map the GUID values for your Account and Contact fields to the respective lookup fields created above. The simplest way of doing this is to have two separate columns within your source dataset - one containing Account GUID’s to map and the other, Contact.
Then, finally, you can put together a Microsoft Flow that then performs the appropriate mapping from the temporary fields to the Customer lookup field. First, define the trigger point for when your affected Entity record is created (in this case, Contact) and add on some parallel branches to check for values in either of these two temporary lookup fields:
Then, if either of these conditions is hit, set up an Update record task to perform a single field update, as indicated below if the ADF Account Lookup field has data within it (swap the value of the Company Name Type to contacts for the second branch):

You can download a copy of the above flow using this link, thereby allowing you to import and modify it within your environment quickly.

AttributeType.Owner

This is one limitation that, I think, has the potential to cause the most issues and, similar to the Customer field type, can be explained due to the twin nature of this field type. The Owner field fulfils a self-evident purpose within the application - namely, to tell us which particular User or Team within the applications owns a record. It, therefore, behaves similarly as the already discussed Customer field and, likewise, there is no clear way to tell whether a supplied GUID or Name value is for one entity or the other. And, as you may expect, the workaround solution is largely similar:

Create two lookup fields on your target entity to both the User and Team entity, respectively:
Then, on the ADF side, do the same as the Customer field type and segregate out your User/Team record GUIDs into separate fields.
Finally, we can redevelop the previous Flow to trigger using these new fields instead. A copy of this flow can be downloaded here; it is a carbon copy of the example shown earlier, with some minor alterations.

Conclusions or Wot I Think

The limitations currently in place are entirely understandable, once you become familiar with the unique behaviour of these field types when compared to others within D365CE/CDS. But they are not necessarily insurmountable. As the CDS connector for Flow has demonstrated, by supplying an additional, valuable piece of information - the specific name of the entity that you wish to map to - you can provide the application with all of the information it needs to correctly associate the records together during an update or import operation. Having this functionality replicated within an ADF copy data activity would, at a minimum, introduce a very desirable piece of functionality. Fortunately, I’m not the only one to think so, as there is a User Voice request out currently requesting this specific feature. I would encourage you to upvote this if you would also like to see this feature added in future. Notwithstanding these two specific limitations, I do still believe ADF is worth consideration when attempting to solve more sophisticated data integration requirements, whether they involve D365CE/CDS or not. The solution provides a rich array of cloud-hosted, consumption-modelled capability to allow you to quickly and cheaply implement Extract, Transform & Load (ETL) processes between a wide variety of different locations.