Microsoft Dynamics AX 2012 Data Import Export Framework + Excel Add-in
Purpose: The purpose of this document is to illustrate how you could potentially leverage Excel Add-in in conjunction with Microsoft Dynamics AX 2012 Data Import Export Framework.
Challenge: Microsoft Dynamics AX 2012 Excel Add-in allows to interact with the business data using the power of Excel for “light-weight” data scenarios. For data import/export and integrations scenarios it is recommended to use Microsoft Dynamics AX 2012 Data Import Export Framework. Data Import Export Framework also does support native Excel data sources to populate Staging tables. Staging tables fields are typically based on extended data types which would make it technically possible to invoke Excel Add-in dynamic Lookups when populating Staging tables data to potentially reduce the number of data errors even before the data hits Staging tables.
Task: In this document I’ll describe how you could potentially populate Data Import Export Framework Staging tables using Excel Add-in. This will give you an interesting perspective at possible synergies between different approaches.
Walkthrough
As it was mentioned at the beginning Excel Add-in allows you to interact with Microsoft Dynamics AX 2012 business data. In particular dynamic Lookups and Filters in Excel Add-in could come very handy when populating Data Import Export Framework Staging tables to reduce the number of data errors during initial data entry
Excel Add-in
Let’s consider Products data import scenario using Microsoft Dynamics AX 2012 Data Import Export Framework. I’m going to use a standard Products template and first off I’ll create a processing group called “Product” as shown below
Processing group
I’ll then add Product entity to the processing group and specify Excel as Source data format
Select entities for processing group
As you can see I also supplied Excel file Product.xls, but in fact in this walkthrough I want to populate the contents of Staging table using Excel Add-in. For this purpose I’ll open up Excel and add DMFProductEntity using Add Tables function
Select Tables
The number of mandatory fields from DMFProductEntity table gets added to Excel spreadsheet
Excel book
Then I’ll populate required values. At this point I could leverage dynamic Lookups provided by Excel Add-in for fields bound to appropriate extended data types and/or Filters to filter my dataset as needed
Data
I could also manage multiple data sources in the same spreadsheet if needed
The result of the publishing is success as shown below
Publishing status
Great! So I could populate the contents of DMFProductEntity Staging table using Excel Add-in
DMFProductEntity table
But before I can complete data import process in Data Import Export Framework I also need to populate some records in framework tables, specifically DMFDefinitionGroupExecution table which has a reference to the entity and JobID for data operation
My next step will be to also add DMFDefinitionGroupExecution table to Excel Add-in and populate required data there
Select Tables
However when I try to add DMFDefinitionGroupExecution table to Excel Add-in I will see the following error
Microsoft Dynamics AX Office Addin – Error
This happens because of exception thrown in Microsoft.Dynamics.Framework.Metadata.AX assembly
Exception
Specifically EndRecId and StartRecId RecId reference fields in DMFDefinitionGroupExecution table are not handled properly in QueryDataSourceMetadataCollection.cs
Exception
The reason for this is because EndRecId and StartRecId fields are bound to a generic AX table (Common) in DMFDefinitionGroupExecution table relations
DMFDefinitionGroupExecution table - Relations
For example, this is how EndRecId field based Relation looks like
Relation
In this walkthrough to pass through (bypass) EndRecId and StartRecId fields and display DMFDefinitionGroupExecution table metadata in Excel Add-in I used Visual Studio debugger.
However in the real world I would likely have to write X++ wrapper Web Service or handle creation of appropriate DMFDefinitionGroupExecution table record directly in X++
Okay, now DMFDefinitionGroupExecution table metadata is available in Excel Add-in
Excel Add-in
And I can try populate the data in there. Please see the data below, plus I could explicitly specify a default Staging status = “Not started”
Data
After trying to publish this data I’ll find out that I’ll also need to populate the data in DMFExecution table which is another framework table
Publishing Details
So I’ll go ahead and also add DMFExecution table to Excel Add-in
Select Tables
Excel book
For DMFExecution table record I’ll provide the following data
Data
And successfully publish this data
Publishing Details
After appropriate DMFExecution table record has been published I will come back to DMFDefinitionGroupExecution table record and republish it again
Publishing Details
At this time all supporting framework tables have been populate with appropriate data and we can switch to Microsoft Dynamics AX 2012 and review populated Staging data
Execution history
When I click on View staging data I can review the actual data sitting in Staging table
View staging data
Just to double check the data I can run Validation
Validate
And now I’ll be ready to Copy data to target
Copy data to target
However I can’t see my job listed for execution on Target data execution screen
Target data execution
This is because I didn’t specify Staging status = Ended for DMFDefinitionGroupExecution record. Data Import Export Framework data import consists of 2 steps (which in fact can be automated into 1 step if needed) and the system keeps track of 2 statuses: Staging status and Target status. It is logical that you can only execute Copy data to target for jobs which successfully completed import of data into Staging table
Staging status = Ended
So I’ll need to update Staging status = Ended in my DMFDefinitionGroupExecution table record
Data
Execution history
So now my job will show up on Target data execution form as shown below
Target data execution
However Run button is disabled due to the fact that I didn’t specify file Path field and it is “” (empty)
Code reference
After I do the final correction and specify file Path field with non-empty field I can complete data import
Data
Please note that I specified a fake non-empty value for Path field just to satisfy the data model constraint (Path field value must be specified), and in the case if data import is done completely using Data Import Export Framework Path field value will be populated based on Source path on Get staging data step
Publishing Details
Finally I can complete data import and move data to target
Target data execution
Copy data to target
After successful data import the following infolog will be displayed
Infolog
And then you can review the results of data import in Microsoft Dynamics AX 2012. In my case Product will be created in Microsoft Dynamics AX 2012
Summary: This document describes how you could potentially leverage Excel Add-in in conjunction with Microsoft Dynamics AX 2012 Data Import Export Framework. Specifically my goal was to populate Data Import Export Framework Staging tables using Excel Add-in and better explain the mechanics of how Microsoft Dynamics AX 2012 Data Import Export Framework works. This also gives you an interesting perspective at possible synergies between different approaches for data import in Microsoft Dynamics AX 2012.
Tags: Microsoft Dynamics AX 2012, Data Import Export Framework, DIXF, Data Migration Framework, DMF, Excel Add-in, Lookup, Filter, Data Import, Data Migration.
Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the issues and describe the solutions.
Author: Alex Anikiev, PhD, MCP