I am trying to import an Excel file (*.xlsx) into a SQL Server table using both SSIS and the "Import Data" feature inside SSMS. However, in both instances, I am getting "The Microsoft.ACE.OLEDB.16.0 provider is not registered on the local machine (System.Data)."
However, when I go into my Programs, I see "Microsoft Access Database Engine 2016 (English) 16.0.4519.1000" is installed, as well as "Microsoft Access Database Engine 2010 (English) 14.0.7015.1000" , or at least shows up in the Apps and Features.
What am I missing to allow me to work with *.xlsx files as as the datasource to import into SQL Server either through SSIS or SSMS? Please note, I understand Excel as a datasource is not ideal and there are better methods, but unfortunately, I am not in a position to make demands that they use other file formats. The best I can do right now is ask for them to convert to *.xls format.
June 21, 2021 at 3:13 pm
Have you got both the 32- and 64- bit versions of the drivers installed?
(SSDT uses the 32-bit version and (by default) DTExec will use the 64-bit version – might as well have both installed)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 21, 2021 at 3:18 pm
Have you got both the 32- and 64- bit versions of the drivers installed?
(SSDT uses the 32-bit version and (by default) DTExec will use the 64-bit version – might as well have both installed)
Looks like we were both thinking the same thing in the OP's cross post, Phil 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 22, 2021 at 11:18 am
You are correct. I only have the 32-bit DTExec installed. I am having an issue though finding how to install the 64-bit version of this.
June 22, 2021 at 12:30 pm
You are correct. I only have the 32-bit DTExec installed. I am having an issue though finding how to install the 64-bit version of this.
If you are hoping that we can help you with that, we need you to describe this issue.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 22, 2021 at 12:58 pm
Phil:
The issue is that when I try to import *.xlsx files using either SSIS / SSMS (Import Data option), I get the error "Microsoft.ACE.OLEDB.12.0 Provider Is Not Registered" / "Microsoft.ACE.OLEDB.16.0 Provider Is Not Registered" (depending upon the version I select).
I have the 32-bit version of DTExec installed but not the 64-bit. When I look in my Add / Remove programs, I see both "Microsoft Access Database Engine 2010 (English) 14.0.7015.1000" as well as "Microsoft Access Database Engine 2016 (English) 16.0.4519.1000". It appears the correct JET Engines are installed but for some reason, I am still unable to import *.xlsx files.
I am not sure where to get the 64-bit version of DTExec so I can import data through SSMS. Ideally, I would like to be able to do this in SSIS / Visual Studio, but if I could just get DTExec 64-bit installed, that would be great, but not sure how to go about doing this.
June 22, 2021 at 1:05 pm
Presumably you've tried searching for "Microsoft.ACE.OLEDB.12.0 Provider Download"?
Because that's what I did. If you do that, you'll find both the download files and information from others on how to perform the install.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 22, 2021 at 1:14 pm
Phil:
Isn't that the same as the Microsoft Access Database Engine 2010 (English) 14.0.7015.1000 that is shown in my Add / Remove programs panel?
June 22, 2021 at 1:17 pm
Phil:
The issue is that when I try to import *.xlsx files using either SSIS / SSMS (Import Data option), I get the error "Microsoft.ACE.OLEDB.12.0 Provider Is Not Registered" / "Microsoft.ACE.OLEDB.16.0 Provider Is Not Registered" (depending upon the version I select).
I have the 32-bit version of DTExec installed but not the 64-bit. When I look in my Add / Remove programs, I see both "Microsoft Access Database Engine 2010 (English) 14.0.7015.1000" as well as "Microsoft Access Database Engine 2016 (English) 16.0.4519.1000". It appears the correct JET Engines are installed but for some reason, I am still unable to import *.xlsx files.
I am not sure where to get the 64-bit version of DTExec so I can import data through SSMS. Ideally, I would like to be able to do this in SSIS / Visual Studio, but if I could just get DTExec 64-bit installed, that would be great, but not sure how to go about doing this.
JET will not work on 64 bit machines.
--Jeff Moden
June 22, 2021 at 1:18 pm
There are two files in the download, one 64-bit and one 32-bit. You need them both.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 22, 2021 at 1:20 pm
Phil:
That might be the problem. I don't have access to install anything on my PC due to security and need to "request" software through our software procurement procedure. They probably only installed the 32-bit version.
June 22, 2021 at 2:35 pm
I think Phil is trying to point out to check if both 32 bit and 64 bit of the Microsoft.ACE.OLEDB driver is installed or not? Please validate this once.
If your SQL Server instance is 64 bit then DTEXEC would be available for both 32 bit and 64 bit. It's just the folder difference. 32 bit DTExec can be found in Program Files (x86) folder and 64 bit DTExec can be found in Program Files folder. Obviously, you would have to trace the SQL Server folder and the relevant folder within for your SQL Server version.
For example, the path of DTExec in my SQL 2016 installation is as follows:
32 Bit : C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\DTExec.exe
64 Bit : C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTExec.exe
Maybe something has changed but, in the past, it wouldn't allow you (unless you knew the "silent" trick) to install the 64 bit version if 32 bit apps were on the system. I'm still thinking that's not a bad idea when it comes to SQL Server.
--Jeff Moden
June 22, 2021 at 3:00 pm
Maybe something has changed but, in the past, it wouldn't allow you (unless you knew the "silent" trick) to install the 64 bit version if 32 bit apps were on the system. I'm still thinking that's not a bad idea when it comes to SQL Server.
As you suggest, the /passive flag on the command line bypasses the restriction.
Not that this applies to you (!), but it's useful to have both on a development machine (not necessarily on a server) because SSDT is 32-bit and DTExec is (by default) 64-bit and they both need to use the driver.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 23, 2021 at 11:30 am
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply