Dynamics AX 2012 DIXF – Issues and Solutions (The version of Microsoft Excel is not supported.-The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine)
Purpose: The purpose of this document is to outline some issues you may encounter when using Dynamics AX 2012 Data Import Export Framework (DIXF) for import of data and provide solutions to resolve them.
Issue: ' The version of Microsoft Excel is not supported.-The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine'.
Reason: When using DIXF Source data format of Type = "File" and File format = "Excel" this issue may occur in the situation when you have 32-bit version of Microsoft Office installed on 64-bit machine (OS). By default you may not have 64-bit Microsoft Excel Driver installed on the local machine which causes this issue even though you may have 32-bit Microsoft Excel Driver in place.
Dynamics AX Error
Solution: Please install 64-bit Microsoft Excel Driver on 64-bit machine (OS) with 'Microsoft Access Database Engine 2010 Redistributable' (AccessDatabaseEngine_x64.msi) by using "/passive" argument. Important: For your particular situation please research for any known compatibility issues when having both 32-bit and 64-bit drivers installed side by side
Let's review the situation in details
First off I'll be using Source data format of Type = Excel to import the data from Excel file
Source data format
Please note that Source data format Type = "File" and File format = "Excel"
To begin with I'm going to use standard DIXF "Product" Target entity
Target entity
Then I'll introduce a processing group
Processing group
To compose initial Source file I'll use "Generate source file" Wizard
Generate source file Wizard (Welcome)
For the sake of simplicity I'll leave only mandatory fields marked
Generate source file Wizard (Display data)
Generated Sample file will look like this
Sample file
Next I'll assign generated sample file to the "Product" entity in Processing group
Select entities for processing group
In case you don't specify Sheet lookup property you will be warned by the system as shown below
Logically we need to know where (what sheet) the data resides inside Excel file
Infolog
When this is set up you would logically try to automatically "Generate source mapping" based on contents of sample file
Generate Source Mapping - Error
This error will also show up in Event Viewer as shown below
Event Viewer - Error
First thing to check is ODBC Data Sources (64-bit)
And it seems like there's no 64-bit Microsoft Excel Driver (please note Platform = 'N/A') which causes this error
Data Sources (64-bit)
It also won't show up in the list of available/installed drivers
Create New Data Source (64-bit)
Here's the same list of 64-bit drivers on Drivers tab
ODBC Data Sources – 64-bit Drivers
We can also check Microsoft Excel to see that it is in fact 32-bit
Task Manager
Now we'll switch over to 32-bit ODBC Data Sources
Data Sources (32-bit)
Please note that we do have 32-bit Microsoft Excel Driver installed
It is also present in the list of 32-bit drives as shown below
Create New Data Source (32-bit)
As well as you can see it explicitly on Drivers tab
ODBC Data Sources – 32-bit Drivers
Now the idea is to install 64-bit Microsoft Excel Driver on 64-bit machine (OS)
In order to do this you can download Microsoft Access Database Engine 2010 Redistributable from here: http://www.microsoft.com/en-us/download/details.aspx?id=13255
Microsoft Access Database Engine 2010 Redistributable
Please note that we'll be interested in installing AccessDatabaseEngine_x64.msi for 64-bit architecture
Choose the download you want
Finally for installation to be successful on 64-bit machine (OS) when you already have 32-bit driver, please use "/passive" switch in command prompt as shown below
Installation
It is also recommended to run command prompt (cmd) as Administrator
After installation finishes you will see 64-bit Microsoft Excel Driver showing up as on the screenshot below
ODBC Data Sources (64-bit)
Now it will also be present in the list of available drivers
Create New Data Source (64-bit)
During Generation of Source mapping another issue you may face with is "Query validation failed-Object reference not set to an instance of an object."
Dynamics AX - Error
This typically occurs when you forget to put "$" sign at the end of Excel Sheet. Please note that the proper syntax to define Sheet lookup is "XYZ$". For example, in my case Sheet lookup = "Product$" which means that Product data resides in Excel sheet named "Product". Resulting query will look like the following: "select * from [Product$]"
Here's how it looks like on Select entities for processing group form
Select entities for processing group – Sheet lookup
After all issues were sorted out we can successfully complete Source mapping generation
Success
And verify it in Visual Designer
Visual Designer
Finally we can add some data into our Excel file
Excel file
And verify that we can successfully preview the contents of Excel file on Select entities for processing group form
Select entities for processing group – Preview file contents
Then you know what to do next to complete the import process J
Tags: Dynamics ERP, Dynamics AX 2012, Excel, DIXF, Dynamics AX 2012 Data Import Export Framework, Data Import, Data Conversion, Data Migration, Issue, Problem, Solution, Resolution.
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
Call to action: If you liked this article please take a moment to share your experiences and interesting scenarios you came across in comments. This info will definitely help me to pick the right topic to highlight in my future blogs.