How to use a recurring Integration Endpoint to Import Data

07/10/24

Integration

How to use a recurring Integration Endpoint for importing data

Recurring Integration Endpoints for Finance and Operations are an interesting option for integrations. From the Microsoft docs, it looks like it's meant for an input/output scenario where we have an inbound message and expect to have an outbound message. That's not specifically the case. We can unlock nearly all of the DMF through a recurring integration endpoint. The only real difference between a recurring integration endpoint and any of the other APIs for DMF is recurring integrations go to a staging area first then get processed by a batch job rather than the request be processed immediately. If creating a record or set of records can wait up to a minute, recurring integrations are worth checking out for your integration needs. We're going to walthrough how to create an import recurring integration endpoint and how to test it.

Getting Started

Azure

First, create an app registration using this article. Be sure to store the value for "Application (Client) ID" and the secret "value" to use later.

Next, add this application ID into System Administration > Setup > Microsoft Entra ID applications, like so:

Postman

Next, we can connect to this environment using that App Registration and Postman. Install Postman if you don't have it already then open Postman. Postman is a popular API development and testing tool that allows developers to design, test, and document APIs, making it easier to integrate and automate workflows.

Create a new workspace.

Create a new collection.

In the collection, you will want to configure authorization. Click on the Authorization tab for the collect and enter the following:

Auth Type: OAuth 2.0

Token Name: your choice

Grant Type: Client Credentials

Access Token URL: Use this format; https://login.microsoftonline.com/your-tenant-guid/oauth2/token replacing 'your-tenant-guid' with the value for your tenant ID. You can use this site to look it up: https://www.whatismytenantid.com/

Client ID: the client ID created earlier

Client Secret: Secret generated earlier

Scope: blank

In the token request section, add a key "resource" and use the value that is the base URL for your environment. ie; https://yourEnvironment.operations.dynamics.com/.

Next save then scroll down and click on "get new access token" then "Proceed" then "use token". It should look similar to this:

Next, click on the variables tab for the collection. Create a new variable called "EnvironmentURL" and paste in your environment base URL like https://yourEnvironment.operations.dynamics.com/, like so:

Create a new request.

To test that we can communicate via API Endpoints, we'll hit an OData endpoint just to confirm we can get some kind of response that isn't an error. We'll create a request like so:

Send the request. We should get a status of 200 OK with some data, like so:

Creating The Data Project

Next, go to System Adminstration > Worksapces > Data Management and create a new export project with the data entities we want to import. We will need a sample input file first so we'll use the DMF to generate that file and we'll configure from there. Create an export project for entity "Customers V3" with a source data format of CSV and for entity "Sales Orders Composite V4" with a source data format of XML-Element. Next, go into mapping for each data entity and remove data points that aren't required for you payload.

For customers, we're only going to be interested in the bare minimum, so we're going to delete everything then add the following:

ADDRESSCOUNTRYREGIONISOCODE
CUSTOMERACCOUNT
CUSTOMERGROUPID
LANGUAGEID
ORGANIZATIONNAME
SALESCURRENCYCODE

For Sales Orders V4, this is a composite entity so we'll have more stuff to manage to the same principals apply. You can read more on composite entities here. we're interested in bare minimum so delete everything and add the following for entity "SalesOrderHeaderV3Entity":

CURRENCYCODE
ORDERINGCUSTOMERACCOUNTNUMBER
SALESORDERNAME
SALESORDERNUMBER

Delete everything then add the following for entity "SalesOrderLineV2Entity":

INVENTORYLOTID
ITEMNUMBER
SALESORDERNUMBER
SHIPPINGSITEID

Once complete, click Download from the project page. This will download a zip file. Extract that zip file. It will contain 2 files we're interested in: "Customers V3.csv" and "Sales order composite V4.xml". Put these someplace we can work with them outside of the zip file.

Next, Create a new data import project. Click Add file, select CSV for source data format, select Customers V3 for Entity name, then upload the Customers V3.csv and click "upload and add'. Wait for that to complete then select "XML-Element" for source data format, select "Sales orders composite V4" for Entity name, then click "upload and add" then select the file "Sales orders composite V4.xml"

Mapping

Now that we have an import project, we'll have to make some changes to the maps in the project to get some specific behaviors we'll need for an import. Some of these behaviors are automagic when using OData but we'll have to configure them for this style of integration.

In the map for Customers V3, set "auto-generated" to true for staging field "CustomerAccount".

For entity Sales Orders Composite V4, we'll have to do some similar changes. Open the map for the entity from the DMF project.

For Data entity SalesOrderHeaderV3Entity, set "auto-generated" to true for staging field "INVENTORYLOTID". set "ignore blank value" to true for staging field "SALESORDERNUMBER"

Create Sample Payload

Next, we'll need to create a sample payload for each entity. For Customers V3, we can open it in Excel and type in values. This is a sample using Contoso data for legal entity USMF:

CUSTOMERACCOUNT,ADDRESSCOUNTRYREGIONISOCODE,CUSTOMERGROUPID,LANGUAGEID,ORGANIZATIONNAME,SALESCURRENCYCODE
,USA,10,en-us,111,USD

For Sales Orders Composite V4, we can set a filter on out export project created earlier and pick a sales order that presents with the data we're looking for such as an SO header with 2 lines. Click on Filter:

Then select a sales order number for what you want a sample for:

Next, run an export on the profile then download the file.

You will get a file that looks similar to this. We can remove auto-generated values.

<?xml version="1.0" encoding="utf-8"?>
<Document>
   <SALESORDERHEADERV3ENTITY>
       <SALESORDERNUMBER></SALESORDERNUMBER>
       <CURRENCYCODE>USD</CURRENCYCODE>
       <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
       <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
       <SALESORDERLINEV2ENTITY>
           <INVENTORYLOTID></INVENTORYLOTID>
           <ITEMNUMBER>D0005</ITEMNUMBER>
           <SALESORDERNUMBER></SALESORDERNUMBER>
           <SHIPPINGSITEID>4</SHIPPINGSITEID>
       </SALESORDERLINEV2ENTITY>
       <SALESORDERLINEV2ENTITY>
           <INVENTORYLOTID></INVENTORYLOTID>
           <ITEMNUMBER>S0001</ITEMNUMBER>
           <SALESORDERNUMBER></SALESORDERNUMBER>
           <SHIPPINGSITEID>4</SHIPPINGSITEID>
       </SALESORDERLINEV2ENTITY>
   </SALESORDERHEADERV3ENTITY>
</Document>

Create Recurring Endpoint

Next, we have to create a recurring endpoint. This will dynamically expose this project and its data entities as a queue we can drop messages off into. From the DMF Project, go to the Manage tab then "Manage Recurring Data Jobs".

Click New. Fill out the required fields. For the Application ID, use the Application (Client) ID we created earlier.

Before we enable the batch job for this, we'll need to configure a few things. At 1 is set processing recurrence. This will tell the batch job that processes message how frequently to run. Next at 2 is Set Monitoring Recurrence. This will set the frequency to run the batch job that collects some telemetry on the recurring endpoint for reporting purposes. This is very basic and will create data for a bar chart that shows a queue size over time. You can review this at "Queue Trend". Next, at 3 is Manage Messages. This is where all messages will appear and you can review their outcomes and contents from this screen. Under advanced options, we have 3 options. The first is to "process messages in order". This will effectively single thread all messages in the endpoint. If order matters for message processing, you will want to enable this option but it will slow down message processing greatly. This may be intended but then only 1 message per batch job run will be processed per batch job run. Next is "use company from message". You can make 1 endpoint like this cover multiple legal entities if you like so long as you specify the legal entity in the message. If no legal entity is specific, the legal entity you were in when creating the DMF project will be used.. Lastly, we have the option "supported data sources type". This is the "killer feature" for this type of endpoint. We can specify file support then drop off multiple files of different types to different entities. In the API call we call out the entity in the project that we are dropping off a file for so we can have multiple entities in a project with different file types all supported by 1 API endpoint.

Finally, set "enabled" to true on the authorization policy then set "is recurring job enabled?" to true as well. Confirm you want to schedule the batch job. Click save.

Addendum Post Release

Frank Hüther correctly pointed out that the "use company from message" being set to No will default the record creation from where the batch job is being run rather than where the DMF project was created. Additionally, you can have multiple batch jobs scheduled / created for a DMF Project in different legal entities that will process messages for different legal entities. This will allow you to create 1 DMF project but create multiple recurring batch jobs in different legal entities to process messages. In our example we were focus primarily in USMF but we could add batch jobs for USPI. We can find the batch jobs created for our primary Legal Entity then duplicate them and reconfigured them for another legal entity.

We can duplicate and reconfigure the batch jobs for use in other legal entities

Testing

Because these are configured endpoints, the endpoint for each is dynamic and specific for each Recurring integration. The formula is below:

https://yourEnvironment.operations.dynamics.com/api/connector/enqueue/en... Name in Project

The URLs i'm using for each entity will look like this; one for the Customers V3 entity and Another for the Sales orders composite V4 entity.

https://yourEnvironment.operations.dynamics.com/api/connector/enqueue/{64B54AB6-2151-4D8D-9D07-DB3F88010DE7}?entity=Sales orders composite V4
https://yourEnvironment.operations.dynamics.com/api/connector/enqueue/{64B54AB6-2151-4D8D-9D07-DB3F88010DE7}?entity=Customers V3

The GUID value for the endpoint ID can be found on the Manage scheduled data jobs screen and the data entity names are in the DMF Project.

If you'd like to submit messages to a difference legal entity than is standard based on the DMF project, you can use requests like these:

https://yourEnvironment.operations.dynamics.com/api/connector/enqueue/{64B54AB6-2151-4D8D-9D07-DB3F88010DE7}?entity=Sales orders composite V4&company=uspi
https://yourEnvironment.operations.dynamics.com/api/connector/enqueue/{64B54AB6-2151-4D8D-9D07-DB3F88010DE7}?entity=Customers V3&company=uspi

In Postman, create a new request for the Customer V3 entity and another for the Sales orders composite V4 entity.

The Customers V3 request should look like this:

And the Sales order composite V4 request should look like this:

From here, inside Postman get a new Auth token then submit each request. If successful, you should see a response that looks like a GUID.

Customers V3:

Sales orders composite V4:

After that, we can go to the "Manage Messages" screen called out earlier from the "Manage scheduled data jobs" screen for the DMF Project. With some luck, we'll see 2 processed messages:

From each message we can select it in the grid then review the details in "Execution Details in the upper left. Be sure to change the default view for the DMF module to be "Enhanced view" in Data Import/Export Framework Parameters. This is what we expect to see and we can go into the details like any other DMF import if we'd like to.

Additionally, if your using the company id as part of the request, your messages screen will look like this with legal entity IDs:

Scaling Up

One last note is that with this setup, we can send message that have more than 1 record / row in them. For instances, we can send this Customers V3 payload, using ChatGPT or Copilot to generate some data for us:

Then paste it into a new request in Postman

Then review the results in the DMF Execution Summary:

or just look at the list of Customers:

You can also do the same trick with composite entities, like so to create 10 orders with 1 payload:

<?xml version="1.0" encoding="utf-8"?>
<Document>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
    <SALESORDERHEADERV3ENTITY>
        <SALESORDERNUMBER></SALESORDERNUMBER>
        <CURRENCYCODE>USD</CURRENCYCODE>
        <ORDERINGCUSTOMERACCOUNTNUMBER>US-003</ORDERINGCUSTOMERACCOUNTNUMBER>
        <SALESORDERNAME>Forest Wholesales</SALESORDERNAME>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>D0005</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
        <SALESORDERLINEV2ENTITY>
            <INVENTORYLOTID></INVENTORYLOTID>
            <ITEMNUMBER>S0001</ITEMNUMBER>
            <SALESORDERNUMBER></SALESORDERNUMBER>
            <SHIPPINGSITEID>4</SHIPPINGSITEID>
        </SALESORDERLINEV2ENTITY>
    </SALESORDERHEADERV3ENTITY>
</Document>

Since each order in the payload has 1 header and 2 lines, and we duplicated 1 order 10 times, we should expect to see 30 created records when viewing the execution summary - and we do: