How to run SSIS packages in 32-bit mode to avoid Excel and Access errors
Part one of a three-part series of blogs

If you're using SSIS to import from or export to Excel workbooks or Access tables - as you surely will be - you'll need to run your packages in 32-bit mode. This blog explains why and how to do this.

  1. Integration Services Problem Importing Excel/Access Data (this blog)
  2. Running Packages as 32-Bit in Development
  3. Running your Packages as 32-Bit on the Server

Posted by Andy Brown on 08 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Integration Services Problem Importing Excel/Access Data

When you're just starting out with SSIS, the last thing you need is to encounter a weird problem which stops you importing data from Excel or Access.  On my machine this manifests itself like this:

No tick or cross

When you run a package, it stops before it even gets going!

 

If I display the Output window and run the package, I see this:

Output window erorr message

My package has ended abruptly with error code 0x4004300a.

On some of our training laptops a dialog box appears containing a spurious error message.

The solution to this problem is to run your application as 32-bit, not 64-bit.  The final two parts of this blog show how to do this on your test computer and on the production server.

A few more notes on the problem

SQL Server Data Tools (or its earlier incarnation, BIDS) runs as a 64-bit application.  If you have a 64-bit computer, the chances are that you're running Microsoft Office as a 32-bit application.

The problem is that you can't get a 64-bit application to use a 32-bit driver, so you need to run SSIS in 32-bit mode. 

This problem plagues Wise Owl: not just in SSIS, but also in Analysis Services.  You can see why Microsoft recommend installing the 32-bit version of Office here.

  1. Integration Services Problem Importing Excel/Access Data (this blog)
  2. Running Packages as 32-Bit in Development
  3. Running your Packages as 32-Bit on the Server
This blog has 0 threads Add post