question

nikhil.kadam49521 avatar image
nikhil.kadam49521 asked

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

Hi I have created a package in my local machine using visual studio 2008 and i have excel in local machine.My package will load data from table to excel.I have used same package in other server and excel not available in that server.While executing the package am getting following error. There is no chance to install excel in that server. The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. Please guide.
ssissql-server-2008-r2
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You say you can't install Excel (which is fair enough), but what about just the connectivity drivers? https://www.microsoft.com/en-gb/download/details.aspx?id=23734 would appear to be the right thing.
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
There is no need to install Excel on the server, but you do need to install and configure the ACE provider. Version 12 is pretty old (current version is 15), but you can still get it from [Microsoft Access Database Engine 2010 Redistributable][1] page. When downloading, please pick the appropriate version of the file. The SQL Server you have is probably 64 bit, so download 64-bit version of the redistributable. Once downloaded and installed, the provider needs to be configured. After installation, the provider will be listed under **Server Objects** -> **Linked Servers** -> **Providers**, listed as **MIcrosoft.ACE.OLEDB.12.0**. Double click on it to invoke the Provider Options dialog and ensure that **Allow inprocess** option is enabled (checked). This option is crucial. Without it the queries which are using the ACE provider will hang forever in SQL Server 2008 and 2008 R2, and worse still, may cause the SQL Server service to abruptly stop in unpatched installation of SQL Server 2012, so the option to allow in process must be configured ***before*** any attempts are made to execute any queries. Once the provider is installed and configured, you can check and configure, if necessary, the **Ad Hoc Distributed Queries** option. To do that, please run this first: exec sp_configure; go If the config\_value of the **Ad Hoc Distributed Queries** is 0 then set it to 1 (script is below). If the option is not even visible then it means that **show advanced options** option itself is not set yet. Enabling the **Ad Hoc Distributed Queries** option will be, in this case, possible after the **show advanced options** is enabled. Here is the script: exec sp_configure 'show advanced options', 1; reconfigure with override; go exec sp_configure 'Ad Hoc Distributed Queries', 1; reconfigure with override; go Once all is done, you can test the provider via openrowset: -- path is relative to the server, drop the sample spreadsheet named test to this path select * from openrowset ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\SomeFolder\test.xlsx;', 'select * from [Sheet1$]' ); If the select does not work and you get the error message reading "Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" then it means that the principal executing the select (you in this case) is different from the account under which the instance is running and that principal does not have enough rights to have the permission to the local temp folder of the service account. This behaviour is by design, the provider needs read/write permission to the local temp folder. This error will also occur during the run of the SQL Job executing the package, if the account under which the job is running does not have permissions to the local temp of the instance service account. To cure this problem, please navigate to the temp folder in question (**C:\\Users\\ServiceAccountName\\AppData\\Local\\Temp**) and set permissions for other account(s) as needed. If all is well then the package should run. Hope this helps. Oleg [1]: https://www.microsoft.com/en-us/download/details.aspx?id=13255
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.