最新消息:ww12345678 的部落格重装上线,希望大家继续支持。

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)

网络文摘 William 4335浏览 0评论
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)  
 
<![if !vml]><![endif]>
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.
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址