Updating Dynamics365 from Azure Data Factory

 Updating an entity in Dynamics365 from Azure Data Factory should be easy right? I mean, they're both Microsoft products, there's a native connector for Dynamics365 in ADF and it all seems to connect up ok to pull data from Dynamics so should be straight forward. Well, turns out it's not so grab a coffee because this one takes some explaining.

Now to be fair, a lot of this is documented on various other posts, but there was a fair bit of subtlety and nuance. Hopefully this post has all the bits so if I ever need to do this again it will be a piece of cake.

TL;DR;

For any lookup field/column in the entity:

• Create a derived column with a name of "<field schema name>@odata.bind", e.g. for field my_accountid the column name needs to be "my_AccountId@odata.bind" with the quotes
• The value of the column needs to reference the lookup table with an 's' added to the end, e.g. my_accounts field is an account id from the account table so the value needs to be /accounts(<accountid guid>) 
• in the mapping, the target needs to be the schema name with @odata.bind on the end, e.g my_AccountId@odata.bind, so the mapping in this example would be ["my_AccountId@odata.bind" -> my_AccountId@odata.bind] 

Scenario Setup

So for this actual bit of work we were replacing a feed from a SQL Server database view that updated a Dynamics entity. The original feed was a PowerApps pull from the database, but security didn't like that so we needed to create something to push to Dynamics instead.

Service principal access works correctly, data is good and entities are all ready to go. For this post I'll simplify the schema structure and not dwell too much on the specifics other than where there were issues

Approach 1 - Copy Data Activity

 For a simple first cut I tried a simple Copy Data activity. This actually worked really well - grab data from the database view and push it into the table, everything flowed nicely and the data was visible in the front end.

Except ...

The primary key values in the entity weren't known by the database so we'd get duplicate records. We could've used an alternate key, but that would require marking the field as an alternate key in Dynamics and the team wasn't keen to do that at this stage, due to other work and lack of time for testing.

It was easy enough to add steps to do a data pull from the entity, match the data using our alternate key and then write the data back to the entity, and this approach did work, but was clunky and slow. We'll keep this as the fallback solution, but meanwhile try ...

Approach 2 - Data Flow with Lookup

Next approach was to use a data flow with a lookup onto the entity, using our alternate key as the join and then the primary key for an upsert. First stab at this worked well, but some columns weren't updated. From here, things turned sour pretty quick.

The columns that weren't updating were Lookup fields in the entity. For the explanations that follow we'll call the Dynamics entity my_servicedelivery and the lookup field my_accountid, which is a guid column referencing the account entity.

The data flow was pretty simple; 2 sources - 1 for the view and 1 for the entity, a left join between the view data and the entity, an Alter Row upsert using the my_servicedeliveryid key and then the sink to the entity,

 

The Errors

Microsoft.OData.ODataException: An undeclared property 'my_accountid' which only has property annotations in the payload but no property value was found in the payload 

 The first error encountered was "Microsoft.OData.ODataException: An undeclared property 'my_accountid' which only has property annotations in the payload but no property value was found in the payload"

Turns out that entity attributes have both a logical name and a schema name. The logical name is what you see in the attributes for mapping and in the Dynamics database tables, the schema name is a camel case version of the logical name, so in this case logical name my_accountid had a schema name of my_AccountId. 

This error means you need to use the schema name, rather than the logical name. MS documentation states that you should use schema name for any custom attributes, but I found it was only necessary for the lookup ones

You can use a tool like FetchXML builder in Xrm Toolbox  to find the schema name and also see if the attribute is a lookup type.

Updating the column name in the view fixed this problem, leading to ...

Microsoft.OData.ODataException: A 'PrimitiveValue' node with non-null value was found when trying to read the value of the property 'my_AccountId'; however, a 'StartArray' node, a 'StartObject' node, or a 'PrimitiveValue' node with null value was expected.

 Now, this error has been reasonably well noted, but there are a few nuances to it. Basically, Dynamics is getting a string value from ADF, but wants an object.

There are 2 bits to this; 

1. The column name needs to be the schema name with @odata.bind added to the end of it, so in this example the my_accountid source column needs to be called my_AccountId@odata.bind
2. The value of the column needs to reference the lookup entity, which in this case would be account, but it needs to have an s on the end and be in the format /entitys(guid). So for this example the value in the column should be '/accounts(<guid for the accountid)'

Ok, easy enough, update the view to have a column called [my_AccountId@odata.bind] and set the value to be CONCAT(N'/accounts(', my_accountid, N')'). Job done.

Except it wasn't. The debug run still failed with the same error.

ADF, and maybe Dynamics, doesn't handle the column name in the view very well and doesn't present it properly (or Dynamics doesn't treat it properly). So, we roll back the view changes and instead create a derived column in ADF before the upsert, setting the column name to have the @odata.bind on the end of the name and the concat logic in the expression. After a bit (ok, quite a bit) of playing with different names and expressions it turns out the column name also needs to be in double quotes, "my_AccountId@odata.bind"

 

So that must be it, right? Well, not quite. Same error.

The last step was to set the target column in the sink mapping to also be in the format schema_name@odata.bind, as shown in the JSON script snippet from the dataflow

 

 Once all of this was set the flow debugged correctly and data was updating as expected.

In our actual data flow we had multiple lookup fields, so created multiple derived column transforms prior to the Upsert but the mapping principle was the same for each field.

This took a bit of time to get sorted, but having the lookup to get the entity id does work better for performing updates and is a lot safer in avoiding duplicates.