Dynamics 365 Change Data Capture

My Challenge

My first assignment was to grab a payload of record changes from Dynamics and push them into a stored procedure in a SQL staging database. The requirements specifically stated that this payload must be in the Dynamics Web API JSON format, and any entity references must also include the referenced record’s primary ID and name fields. After doing some research, I found that I had several options available.

Option 1 – OData Track Changes

In exploring options for tracking changes with the Dynamics Web API, I encountered a header option for GET queries called odata.track-changes. When configuring an API call, a header with a key of prefer is configured with a value of odata.track-changes.

This is a great way to pull delta changes, however a few drawbacks caused me to keep looking for another solution. When odata.track-changes is preferred, the $expand and $filter functions are disabled, meaning the requirement to include information about referenced entities cannot be met. Additionally, the delta token is not editable to a specific point in time and any change to the $select statement renders the token invalid. The final nail in the coffin is that this API call doesn’t differentiate between which fields have changed and which haven’t – all fields from the $select statement are returned, even if only one has changed.

Option 2 – Using modifiedon Date to Return Changes

The next option I explored was using the record’s modifiedon date to build a query that returns only records that have been modified since the last record processed. I would use a $filter statement to narrow down my results and, because odata.track-changes wouldn’t be enabled, I could use the $expand function to return related entity information.

This method worked fairly well, but also had its drawbacks. Again, as with odata.track-changes, there was no way to tell which fields on a given record had changed, as the GET was returning all fields from the query every time. Additionally, I had my Azure function app configured to run on a timer and I was concerned about data collisions, where one function app might spin up before the previous had completed.

Option 3 – Dynamics Native Service Bus Integration

As my search for a solution wore on, I was pleasantly surprised to find that Dynamics has a pre-baked integration with a number of Azure endpoints, including event hubs and service bus queues. This integration was a breeze to configure, and only required the plugin registration tool, a connection string for the Service Bus Queue, and the registration of the steps for the messages and entities in which I was interested.

I found the flexibility of this integration to be a major selling point – if I’m interested in updates but not creates, that’s no problem. If I want to exclude specific fields – composite and yominame fields, for example – I can do it with just a few clicks. The submission of the payload to the service bus queue runs as an asynchronous process, but is generally very quick. I was pleased to find that the JSON payload only contains the changed attributes, but disheartened that the payload itself contains the compliment of Dynamics message information and is formatted with key-value pairs. So while this solution met some requirements that the Web API didn’t, it still wasn’t the complete package.

The Final Solution

To meet the requirements of this project, I ended up going with a hybrid approach. I configured the native service bus integration to place its message payload on a service bus queue. I have an Azure function app that listens to this queue, picks up the dynamics messages asynchronously, and crawls the JSON to pull out the relevant entity and attributes. It then compares that information to Dynamics metadata and uses all of this to dynamically generate a Web API URL to return the relevant information, including expanded entity references, in the format required. This message is placed on a second queue, where a second function app listens and submits the payload to a SQL stored procedure for further processing.

Conclusion

I found working with the Dynamics Web API to be a very rewarding experience. My solution is still imperfect, and may warrant further changes in the future, but going through this process has shown me that Dynamics is at once open, secure, powerful, compliant, and highly connectable to other applications. Azure function apps and service bus queues provide the option to incorporate native functionality and keep everything within a single tech stack.

Are you looking for help integrating Microsoft Dynamics 365 with an ERP, MRP, or HRMS system? Beringer Technology Group is here to help! Our team of Microsoft-certified experts can lead you through the process and help you utilize cutting-edge technology to seamlessly bridge the gap between your business software solutions! Contact us today!