Running SSIS Packages in 32-bit Microsoft.ACE.OLEDB.12.0 is not registered

Issue: 

Source: Package Connection manager “Excel Connection Manager” Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode

Missing Components

X86 DTExec.exe not installed

I embrace a minimalist’s approach to software installation on all my servers. This means that often my servers will only have the database engine installed and possibly a few other features (such as Integration Services) as needed by the system. In most situations, there is no need for developer tools on a production server, therefore, BIDS and other components are omitted.

When installing components in this way it is easy to overlook DTExec.exe. This application is required to execute SSIS packages and only the 64-bit version will be installed if you omit some of the optional features. In order to prevent the SQL Agent error given as an example above, you will need to install either the Business Intelligence Development Studio (2008), SQL Server Data Tools (2012), or Client Tools (MSDN). I prefer to install the Client Tools when dealing with production servers.

Once installed, the 32-bit version of DTExec.exe will be available and your SQL Agent job will be able to execute in 32-bit mode. See below, Execution Properties – SQL Agent “Use 32-bit” check box, for more details on how to execute an SSIS package in 32-bit via SQL Agent job.

ACE OLEDB Provider not installed

While this is not technically a platform issue, as is the subject of this post, it does cause the same symptoms and thus is worth lumping in with this list of solutions. When attempting to interact with Microsoft Office files, such as Excel or Access, you need to have the right provider installed. Normally MS Office is installed as 32-bit even though the 64-bit version is available. Either way, if your server does not have Office installed you will not have the right providers necessary to execute your SSIS package.

Sticking to the minimalist’s mentality, I would never want to install a suite of tools like MS Office on my servers unless there was no other option. In this case, the option is to install the providers directly. The providers have a smaller footprint on your server and there are no licensing issues.

Typically you would use either the Jet 4.0 provider or the ACE OLEDB provider to interact with MS Office files. You might remember that in our example errors the system complained about the Microsoft.ACE.OLEDB.12.0 not being registered and the class not being found. The Jet provider is deprecated and not recommended for use.

Download site for the Microsoft Access Database Engine 2010 redistributable.

Execution Properties

SQL Agent “Use 32-bit” check box

In addition, to having the necessary components installed 64-bit installations of SQL Server must be told to use the 32-bit version of DTExec.exe when running a SQL Agent job. To set this option you must open the job and edit the job step for your SSIS package.

Then navigate to the Execution Options tab and select the Use 32 bit runtime check box.

Visual studio 64-bit runtime mode

Just like the SQL Agent job, your Visual Studio will likely need to be set to run as 32-bit also. In order to do this you need to open the project properties by right clicking on the project in the solution explorer and selecting properties or going to the menu bar and clicking Project and then <project_name> Properties. Once here you need to set Run64BitRuntime to FALSE.

http://www.sqlhammer.com/running-ssis-packages-in-32-bit/