Hello Techies,
While working on SQL import wizard using excel with (.xlsx)
extension my client started experiencing issues with the below mentioned
issue. Surprisingly he was not encountering any issue while working with .xls
or .csv file
---------------------------------------------------------------------
TITLE:
SQL Server Import and Export Wizard
---------------------------------------------------------------------
The
operation could not be completed.
---------------------------------------------------------------------
ADDITIONAL
INFORMATION:
The
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
(System.Data)
---------------------------------------------------------------------
While working on this to find solution I found many
websites claiming many solutions which provided and their own experiences but
again digging this more I came to know that Excel 2010 driver is 64 bit but by default
SSMS import export wizard is 32 therefore the error message appears.
To fix this issue you can import using the Import Export
Data (64 bit) tool for trial.
Depending on SQL Binary location
You can find your binary location doing below steps,
Right click on Instance name --> Root Directory
This value shown on the right hand side shows your SQL installation folder so access the "DTSWizard.exe" application.
“C:\Program Files\Microsoft SQL
Server\110\DTS\Binn\DTSWizard.exe”
I hope you were able to proceed with the import export
wizard 😊 and to fix this permanently you should copy the
“DTSWizard.exe” 64 bit file from the below location to x86 folder with replace
option.
Note: Its always good to copy the file to
be replaced to be on safer side to some other location.
Source
C:\Program Files\Microsoft SQL
Server\110\DTS\Binn\DTSWizard.exe
Destination
C:\Program Files (x86)\Microsoft SQL
Server\110\DTS\Binn\DTSWizard.exe
Hope this fixed your issue so please help me with your feedback to encourage me writing further sharing my experience.
