How to use Power Automate to extract data from Microsoft Dynamics 365 Finance and Operations via Data management package REST API

December 24 2019

In Microsoft Dynamics 365 Finance and Operations there are two APIs strategies that support file-based integration scenarios:

  1. Data management framework's package API
  2. Recurring integrations API

Both APIs support data import and data export scenarios, and both use batches. Batch data APIs are considered asynchronous integration patterns because when these APIs are called, data is imported or exported in batch mode. However, calls to these APIs are synchronous. For example, a call to the ExportToPackage API schedules a job to import only a specific data package. The scheduling job is quickly returned (synchronously), and the work is done later in a batch (asynchronously). Therefore, batch data APIs are categorized as asynchronous.

In this article, we will review how to leverage Power Automate and the Data Management package REST API to extract data from Dynamics 365 Finance. We will use the example of extracting “Customer groups” in Dynamics 365 Finance. However, any data entity that is exposed can be leveraged to export data from the system. Here are high level steps:

  1. The flow is triggered manually from Power Automate. However, it can be automated as well based on certain events that can be used with Power Automate.
  2. The request is submitted from Power Automate to Dynamics 365 Finance to extract the ‘Customer Group’ data. The entity is configurable and other entities can be used as well.
  3. A data project execution job is scheduled and run in Dynamics 365 Finance.
  4. Wait for the data project execution for a specific duration to see if the data project execution is completed successfully.
  5. Retrieve the package URL from the data project execution.
  6. Leverage Data Package URL to extract the file and store it on One Drive for Business (or another supported data storage service).
  7. From here the information can be retrieved and reviewed or shared with other systems.

If Power Automate steps are named appropriately, it can be sufficient to explain all the steps, screenshot below provide details:

Power Automate – Important steps in this flow

Let’s review important steps in diagram above so we understand how all this works:

1. Execute ExportToPackage API function

In this step, we are calling action ExportToPackage within Dynamics 365 Finance.

The definitionGroupId is “CustomerGroupExport”. This must match to a Data Project in Dynamics 365 Finance. Data Project setup from Dynamics 365 Finance:

Once the call is made during flow execution, under Data Management workspace you will see a data project execution under Job history.

The output of the ExportToPackage API in Power Automate is ‘Execution group id’ that you see in screenshot above.

2. Execute 'GetExecutionSummaryStatus' API function

In this step, we are calling another action within Dynamics 365 Finance. The input to this step is the output of the previous execution step i.e. Execution group id.

The above step is executed at specific intervals and for a limited duration until the execution summary status output is “Succeeded”.

 3. Execute “GetExportedPackageUrl” API function

In this step, we are calling another action within Dynamics 365 Finance. The input to this step is the output of the first execution step i.e. Execution group ID.

The output of this step is URL, that can be used to download the data package.

4. Execute “Get” HTTP function

The step below is executed to retrieve the package content based on the URL retrieved in previous step.

 5. Store the package retrieved on OneDrive for Business

Once the package content is retrieved, we are leveraging Power Automate functionality to store the contents on OneDrive for Business. Note the authentication to OneDrive is required for this step.

Execute flow from Power Automate

Go back to Power Automate, retrieve your flow, and click on the ‘Test’ button to start the execution and follow the simple steps. Once it’s completed successfully, here is how your flow will look:

Review output based on flow execution

The last step stores the package on the OneDrive for Business. Here is how it looks once saved there:

The content can be downloaded and then the data can be viewed in Excel. In this case the file name is ‘Customer groups.xlsx”.

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About Satish Panwar

Satish works as a Senior Architect at Hitachi Solutions America and has more than 14 years of experience implementing different versions of Microsoft Dynamics AX. He holds numerous certifications and hold bachelor’s degree in Information technology.

More about Satish Panwar

Comments

giorgios's picture

Hi Satish,
I need to do what you have developed for extracting information coming from Talent. Your explanation is very clear, just a Thing, where do I create the Exportto Package api? I have experience in creating Power bi API and not 365 api.

Regards

Giorgio


satish.panwar's picture

Hi Giorgio,
The ExportToPackage api is inbuild and can be used when we use the connector "Finance and Operations" in Power Automate/Microsoft Flow. Try to see if connector exist for Talent as well.


robibg79's picture

Hi Satish,
thank you for your post, anh how it's possible to extract only a subset of data?
F.i. customers that has only "EUR" currency set on customerv3 data entity.

Regards,
Roberto


DeanInnell's picture

Hi can you post the detailed steps for executionstatus variable and do until status is success


Mads Christensen's picture

Hi,

very good visualization, first of all - thanks!

I run into trouble on the very first action ExportToPackage. I can see in D365FO that he job is excecuted, but i get the following error:

The API 'dynamicsax' returned an invalid response for workflow operation 'Excecute:_Call_export_to_Package_i_DIXF' of type 'OpenApiConnection'. Error details: 'The API operation 'ExecuteProcedure' requires the property 'body/value' to be of type 'Object' but is of type 'String'.'

Can you helt me here?

again thanks for a good article!

/Mads


jake12's picture

If you have questions about some of the steps, the YouTube video where he covers it can be found here: https://www.youtube.com/watch?v=oPx09tiNS6g