Working with Dynamics 365 WebAPI in your ETL Process

27 February 2019
Daniel Cai

WebAPI is the latest web service interface for the CDS and Microsoft Dynamics 365 Customer Engagement platform. It is the new preferred API option when connecting to Microsoft Dynamics 365 Customer Engagement (CE). If you have been a professional Microsoft Dynamics 365 developer for a while, you must have heard that the SOAP 2011 service endpoint has been deprecated about 2.5 years ago. At the time that the SOAP 2011 is completely retired (or removed) from the Dynamics 365 platform in the future (not sure when at this moment, hopefully not sooner than its v11 release), WebAPI will be the final replacement. In comparison to the SOAP 2011 service endpoint which uses SOAP messages to interact with Dynamics platform, the WebAPI is a RESTful API based on OData (Open Data Protocol) v4.0 which uses JSON data structure for service requests. With that being said, for whatever development you might be working on, you should consider WebAPI as your first option when connecting to Microsoft Dynamics 365 CE for the following couple of reasons: 

  • WebAPI is a future-proof API - this is obvious as Microsoft has announced the deprecation of the SOAP service endpoint
  • JSON is a light-weight message format, which makes the size of service request messages (also response messages) much smaller, therefore rendering shorter networking time which should result in better performance

In responding to the Microsoft move, our team at KingswaySoft has worked really hard in the last few months in trying to add WebAPI support to our SSIS Integration Toolkit for Microsoft Dynamics 365 product, which was finally made available in our Winter 2019 release announced last week. In this blog post, I will discuss the development experience with the new API and how you can switch. 

  • First of all, in order to access WebAPI support in our software, you need to upgrade your Dynamics 365 toolkit to the latest v11.0 release available at its download page.

  • After you have installed the the latest release of our software, you can go ahead launch your SSDT environment, from which you can open up your existing packages or project.
  • Once you have the project ready, you can open the connection manager by making a switch of the service endpoint to the WebAPI option.

That is literally all it takes to switch to WebAPI and start to make use of it in your ETL process. SOAP 2011 and WebAPI service endpoints use the same metadata system, there is no need for any component metadata refresh or anything in that nature. 

However there are certain limitations with Dynamics 365 WebAPI as of its most recent v9.1 release, which you should take into consideration when making the switch. We have highlighted those limitations in the connection manager when you hover over the "i" icon next to the Service Endpoint option as also described below. 

  • Updating lookup fields to NULL is not currently supported, as there is not an efficient way to do so when WebAPI is used as of the mentioned release.
  • There are two limitations when using an Alternate Key as the matching criteria for Upsert or Update action in a CRM destination component. First, it does not report any values for the IsNew and CrmRecordId output columns in its Default Output, as Dynamics 365 CE/CRM server does not return such information when WebAPI is used. Secondly, it does not work if any of the key fields have a NULL value. We recommend using the Manually Specify option as the alternative, or simply just use the SOAP service endpoint if required.
  • Using Duplicate Detection as the matching criteria for Upsert or Update action is not recommended, as the RetrieveDuplicates function that we rely on does not return those existing duplicate records reliably. Using the option for Upsert action could result in duplicate data. Again, we recommend using the Manually Specify option as the alternative, or simply just use the SOAP service endpoint if required.
  • When working with EntityChanges option in CRM source component, you should not select any lookup fields, doing so results in an expected server-side error.
  • AuditLogs option in the CRM source component is not currently supported, as the RetrieveRecordChangeHistory function we rely on is giving an unexpected server-side error.
  • Bulk Delete action is not functioning as the FetchXmlToQueryExpression function will report an unexpected server-side error, which is a function that we rely on.
  • Using Delete action for campaignactivityitem entity is not currently working due to disparity of the service request message used.
  • Writing to campaignitem entity is not currently working due to disparity of the service request message used.
  • Using Delete action for listmember entity is not currently working due to disparity of the service request message used.
  • Send action for the 'email' entity is not working, as it renders a server-side error when the request is posted to the server.
  • When making an update to any party list fields in an activity entity (such as the 'from', 'to', 'cc', 'bcc' fields of 'email' entity), you need to make sure that you write to all party list fields in the same entity, otherwise those party list fields that do not have an input will be cleared out, which could result in unexpected data losses.

Hope this helps. Happy integration!

 About 
comments powered by Disqus