Solved

[SSIS] : The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

Posted on 2014-11-06
4
525 Views
Last Modified: 2016-02-11
Hi Experts,

I recently created an SSIS package on my local machine and it works well. Within the package, it creates a connection to an excel file, reads the data and processes it like a dataset. Something like this:

            OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Excel 12.0 Xml;HDR=YES;IMEX=0;");
            con.Open();

Open in new window


When I tried to deploy on our test server I received an error. It is important to mention that our test server does not have any version of MS Office installed. So the error I got was :

Error Message ::: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. Error StackTrace ::: at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at ST_f688224974424dccb939cc4d81318378.csproj.FileImport.ReadXLSFile(String FilePath, String FileHeader, String DataDelimiter, String SheetName) at ST_f688224974424dccb939cc4d81318378.csproj.ScriptMain.Main()

Open in new window


I googled the error and downloaded "Microsoft Access Database Engine 2010 Redistributable" and installed on our Test server. I even restarted the server.
There were two version of files kept on server, I downloaded and installed the 32 bit version of installer. Name of the file is "AccessDatabaseEngine.exe". Somewhere on internet it was mentioned that SQL Server Integration services for SQL Server 2008 R2 supports 32 bit version only so it seems I did the right thing.

In my package property, Run64BitRunTime is set to False.

After doing all of above, the job still fails logging the error in Event Manager I have given above. However, when I open the package from SQL server BI Studio directly on the Test server and run it from Visual studio, it works like a charm.

Any help regarding this issue will be greatly appreciated.

Thanks.
0
Comment
Question by:AmitJain001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40427340
If you create the connection that way. it is correct that the BI / IS bitness determines the driver to use.  I'm not confident, however, that IS is 32bit-only, so I would try with the 64bit driver.
0
 

Author Comment

by:AmitJain001
ID: 40427717
Before trying with 64 bit version, just one clarification. Should I uninstall 32 bit version first or can these two coexist?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40427884
They can coexist.
0
 

Author Comment

by:AmitJain001
ID: 40430444
Thanks Qlemo. Let me try this on our development server and I will get back to you. I really hope that works.

Cheers :)
0

Featured Post

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question