58

I have a SQL Server job that runs monthly that runs in server. Job is using an SSIS package and is supposed to extract the data from database and and create an Excel sheet and copy the data into Excel 2003.

I actually got around 140,000 rows from the database due to truncation issue in Excel 2003 (Excel supports 64,000 rows). So I modified the config file to support 2007 Excel format.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="  +  @[User::FullPath]  + ";Extended Properties=\"Excel 12.0;HDR=YES\"

But when I try to execute the job, it fails showing error message:

"The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered"

Kiquenet
  • 14,494
  • 35
  • 148
  • 243
user3330998
  • 663
  • 2
  • 7
  • 12

14 Answers14

78

Summarized: INSTALL 32 bit version of Microsoft Access Database Engine 2010 Redistributable. Uninstall 64 bit version if previously installed. http://www.microsoft.com/en-us/download/details.aspx?id=13255

The Excel connection manager is trying to use the ACE OLE DB provider in order to access the Excel file when the version is above 2007 (xlsx). Although your box is 64-bit, you’re using SQL Server Data Tools, which is a 32-bit application. There is no 64-bit version for SSDT. When you design your package within SSDT, you’re using a 32-bit process, which can only use 32-bit providers. When you try to choose the table in the Excel file, the connection manager needs to access the 32-bit version of the ACE OLE DB provider, but this provider is not registered on your machine, only the 64-bit version is installed.

You should download the 32-bit version of the “Microsoft Access Database Engine 2010 Redistributable”. When you try to install it, you might get an error message. You should first uninstall only the 64-bit version of the “Microsoft Access Database Engine 2010 Redistributable”, which you probably installed previously. The 64-bit version and the 32-bit version can’t live together on the same host, so you’ll have to uninstall (through “Program and Features”) and install the other one if you wish to switch between them.

Once you finish uninstalling the 64-bit version and installing the 32-bit version of the provider, the problem is solved, and you can finally choose the table within the Excel file. The Excel connection manager is now able to use the ACE OLE DB provider (32-bit version) in order to access the Excel file.

SliverNinja - MSFT
  • 31,051
  • 11
  • 110
  • 173
AlejandroR
  • 5,133
  • 4
  • 35
  • 45
  • that was the better and simpler solution for me! SSDT regonized the excel table after the install... thanks! – Anderson Luiz Ferrari Jun 26 '19 at 14:24
  • 1
    More than four years after this answer was given, it is still correct. This solution worked for me today (2019), using SQL Server 2017 deployed on Server 2016. I did, wrongly, install the 64 bit driver first. Then uninstalled the 64 bit, and installed the 32 bit driver. No reboot or restart of services is necessary. – Chris Adragna Jul 14 '19 at 17:24
12

There is a alter way. Open the excel file in Microsoft office Excel, and save it as "Excel 97-2003 Workbook". Then, use the new saved excel file in your file connection.

ChinaHelloWorld
  • 1,007
  • 1
  • 12
  • 6
10

Another option is to run the package in 32 bit mode. Click on the solution => properties =? Debugging => Set run in 64 bit to false.

Tara
  • 101
  • 1
  • 2
  • 1
    This worked to resolve my package execution failure message: *"The requested OLE DB provider Microsoft.ACE.OLEDB.15.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode."*. – SherlockSpreadsheets Feb 01 '19 at 22:02
7

I think you can get away by just installing the OLEDB Drivers - http://www.microsoft.com/en-us/download/details.aspx?id=13255

TMNT2014
  • 2,102
  • 1
  • 11
  • 13
6

I installed the "Microsoft Access Database Engine 2010 Redistributable" as mentioned above and got side-tracked troubleshooting bitness issues when it seemed to be a version issue.

Installing "2007 Office System Driver: Data Connectivity Components" sorted it for me.

https://www.microsoft.com/en-us/download/details.aspx?id=23734

Luke Puplett
  • 42,091
  • 47
  • 181
  • 266
6

The easiest fix for me was to change SQL Agent job to run in 32-bit runtime. Go to SQL Job > right click properties > step > edit(step) > Execution option tab > Use 32 bit runtime

screenshot

wiciuo
  • 95
  • 1
  • 9
  • Luckily I tried this before changing installed components. I didn't even have to figure out what other dependencies would be impacted. --This works. – llessurt May 02 '19 at 15:50
3

You have probably installed the 32bit drivers will the job is running in 64bit. More info: http://microsoft-ssis.blogspot.com/2014/02/connecting-to-excel-xlsx-in-ssis.html

Joost
  • 1,873
  • 2
  • 17
  • 18
3

The easiest solution I found was to specify excel version 97-2003 on the connection manager setup.

Jason Meyer
  • 39
  • 4
3

I followed the instructions to use the /passive switch here, after downloading the 64 bit Access database engine. I'm running Office 32-bit, SSAS Tabular Model in SQL Server 2012. When I downloaded and ran the 64-bit Access database engine it came up with a message saying that I couldn't install this without first uninstalling Office 2010, but the /passive switch seems to have solved this (I can now import Excel workbooks and Access tables in a tabular model).

Andy Brown
  • 5,309
  • 4
  • 34
  • 39
  • In retrospect I was way too optimistic about this. I've had endless problems trying to get this to work. There seem to be only two solutions now: uninstall Office 32-bit and install Office 64- bit (which I'm reluctant to do), or use different laptops when training on this software (which is my current preferred solution). – Andy Brown Dec 18 '15 at 14:37
1

I was getting this same error after previously being able to complete similar operations. I didn't try downloading any of the mentioned packages since I didn't have them previously and things were working. IT at my job did a 'Repair' on Microsoft Office 2013 (Control Panel > Programs > Add/Remove - Select Change then Repair). Took a few minutes to complete but fixed everything.

smk081
  • 783
  • 1
  • 10
  • 36
0

Just install 32bit version of ADBE in passive mode:

run cmd in administrator mode and run this code:

AccessDatabaseEngine.exe /passive

http://www.microsoft.com/en-us/download/details.aspx?id=13255

Shahram Sohrabi
  • 1
  • 1
0

I had this issue and it took me a lot of time to figure this out. @tara's answer helped me to solve this problem but I couldn't really find the setting to set run in 64 bit to false. So, here is the screenshot for where you can find the setting

image for setting to turn 64 bit run off

Shadow
  • 37
  • 9
0

If anyone is still struggling with this and have done all the above suggestions and Cry every time someone says install Database Access Engine. This is what sorted for it for me.

  1. Install 32bit Database access engine as others have suggested.
  2. Set to run in 32bit mode within Visual Studio
  3. Set to run in 32bit mode on the Job Step within the job on SQL Server Agent. On the Step, General Advanced. Check 32-bit runtime.

I'd post some images but I don't have enough rep :(

Jamie Gaffney
  • 1
  • 1
0

The following solution is for using Excel 2016 file (i.e. .xlsx) in SSIS. This solution worked for me after struggling with this issue for a longtime.

  • Ensure that the Excel file being opened is the latest version i.e. .xlsx.
  • In SSIS select 'Excel 2016' from dropdown when creating a connection
  • Ensure that in SSMS, the command 'EXEC master.sys.sp_MSset_oledb_prop' shows that 'Microsoft.ACE.OLEDB.16.0' is installed. If 'Microsoft.ACE.OLEDB.16.0' is not installed, then run the 64bit version from https://www.microsoft.com/en-us/download/details.aspx?id=54920
Pradeep Singh
  • 111
  • 1
  • 5