Ask the expert: How to integrate Dynamics 365 for Customer Engagement with XML

It’s hard to imagine a Dynamics 365 for Customer Engagement implementation that doesn’t involve integration with other systems.

This integration can either be in real-time, which usually involves Web Services or APIs, or it could be through batch jobs running periodically and involving a large amount of data. In the latter instance,  we either end up writing a custom application that consumes Dynamics 365 API(s), or we use tools like Scribe Insight, Scribe Online, KingswaySoft SSIS Integration tool kit for Dynamics 365, or COZYROC SSIS+ Components Suite.

Microsoft technologies like Microsoft Flow, Azure Web Job, Azure Functions, and Azure Service Bus can also be added to the equation. In most scenarios, the integration involves the data stored in the database, but it can often be flat files like Excel, Text files, and XML files too.

In one of my recent projects, I was tasked with reading data from the XML files and creating the corresponding records in Dynamics 365 CE. One of the existing systems that we were trying to integrate was providing contract information in XML format; our responsibility was to validate the contract data in the XML files, create the corresponding records—in this case contract and related entities—in Dynamics 365 CE, and provide the response back to the system. This information was to be used, and undergo multiple levels of approval and rejection, by the Dynamics 365 CE users.

We had initially proposed Scribe Online as the best integration tool for our client and had even done a few Proofs of Concept (PoC) around the tool, finding it to be a perfect fit for all the requirements before proposing it to the customer.

Due to certain regulations, however, we had to then drop the idea of using a cloud-based tool and instead needed to find a solution that could run on-premise within the client’s network. Since we’d recommended Scribe Online earlier, we decided to use Scribe Insight, the tool’s on-premise variant, as a replacement.

To our surprise, though, we faced some limitations with the XML Source connection of Scribe Insight, forcing us to search for another on-premise alternative. We found that, while the XML Source Connection does support repeating nodes, it doesn’t support repeating sibling nodes. So, if I have Parent A and two repeating children, Child A and Child B of Parent A won’t be supported.

Having implemented and worked with it previously, we decided on KingswaySoft as our tool of choice. KingswaySoft has XML Source Component as part of their SSIS Productivity Pack. XML Source component can read the XML data from either exiting files or through an HTTP URL-based service endpoint and transform it into column data, which can then be easily processed. It also enables you to easily add and remove nodes from the XML to structure the data further as required.

Here, I’m going to share a simple step-by-step guide on how to use the XML Source Component, using a very basic example. As a prerequisite, apart from SSDT with SQL Server Integration Services, we would need the following KingswaySoft ’s component to work with Microsoft Dynamics 365 CE and XML:

• SSIS Productivity Pack
• SSIS Integration Toolkit for Microsoft Dynamics 365

For simplicity, let’s suppose that we have one Parent Entity, which has one-to-many relationships with two child entities: Child A and Child B. Below is the source XML file that we need to process.

First, create a new SSIS package and drag the XML Source component.

For Data Source, we’ve selected Connection Manager as Local File and set the Local File Path property as shown below.

For the design section, specify either the schema file or import the same XML file.

The XML Source component is intelligent enough to derive the information about the nodes. In this example, it has identified ParentEntity, ChildEntityA and ChildEnityB nodes as Repeating Complex Elements.

The design surface also allows us to change the type of the Nodes, rename the Nodes, add and remove the Nodes.

In the General section, we can define the key field through which the Parent Entity and its child entities can be identified.

Here, we‘ve specified Id as the key field for the Parent Entity and Child entity, referring to the same Id as _ParentKeyField to relate to the parent record.

In the Columns section, we can see the columns for each of the Outputs selected and can select and unselect columns. We can also update the Data Type properties as required.

Now we’re done with the configuration of the XML Source Component, we drag the Dynamics CRM Destination component and select Parent Entity as the input.

Here, we’ve mapped the Id and name column.

Now, our first Data Flow component is configured.

Similarly, drag a new Data Flow component in the Control Flow for creating Child A.

Select ChildEntityA as input from XML Source to Dynamics CRM Destination.

Here, we have the only the name field of the Child Entity A, and for Parent Entity lookup, we’ve provided the Parent Key we specified in the XML Source component.

Here’s how we have mapped the Id field’s value to the lookup.

Repeat the same steps for Child Entity B and complete the Control Flow.

Now, let’s execute the package.

Let’s check inside Dynamics 365 CE.

Here, we can see the two Parent records created along corresponding Child Entities A and B records, as specified in the XML Source file.

That’s how easy it is to implement integration between Dynamics 365 CE and XML using the XML components that are part of the Productivity Pack of KingswaySoft.

Not only does this tool make the integration process easy and more efficient to implement, but it also saves on development resource if you need to implement the same integration through a custom application that uses XML related API(s) and Dynamics 365 CE API(s).