64-Bit SSIS and 32-Bit Excel

Importing data from Excel to SQL Server requires going back in time, so to speak. Yes, back to 32-bits at runtime.

As much as it can be a pain, a lot of projects still require that you import data from Excel to SQL Server. If you work in the world of SSIS, then you might have a problem. Most modern equipment, from laptops to servers, can, and often does, run the 64-bit versions of their operating system. While this is a good thing from a performance standpoint, it will lead to trouble if they try to read an Excel file. The drivers that connect to Excel are 32-bit and when your SSIS package runs, it wants to run in 64-bit. This will throw an error when the package attempts to open the Excel file.

In order to resume happy package execution, you need to do a few things. First, in your development environment, you will need to open your project's properties and change the Run64BitRuntime property on the Debugging page to False (see Fig. 1). This will allow you to debug your package locally if you run 64-bit on your development machine.

Toggle Run64BitRuntime to 'False'
Figure 1. Toggle Run64BitRuntime to "False" in the MtM Property pages. (Click image to view larger version.)

On the server, you need to set the Use 32-bit runtime option on the job step in the Execution options tab (see Fig. 2). This allows the package to run in 32-bit mode and use the 32-bit Excel driver.

New Job Step dialog
Figure 2. Check the "Use 32 bit runtime" box in the New Job Step dialog and you're set to use Excel's data. (Click image to view larger version.)

Keep in mind that this will make the entire package 32-bit, so if you make other calls, such as to Oracle servers, you will need to have the 32-bit version of those drivers available as well. This is by no means a perfect solution, but let's face it: neither is an Excel data source.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.


  • Microsoft Bolsters Windows IoT with NXP and SQL Server Support

    Microsoft's Internet of Things (IoT) product line is continuing to grow, with a few new developments highlighted this week.

  • Tamper Protection Now Available to Microsoft Defender ATP Subscribers

    The Microsoft Defender Advanced Threat Protection (ATP) E5 subscription plan now has an optional "tamper protection" security feature, Microsoft announced on Monday.

  • Exploring OCR, a New Way To Get Data into Excel

    Microsoft recently added a new optical character recognition feature to Excel that lets users import data from a photograph taken from a smartphone. Here's how to use it.

  • Microsoft Authenticator App To Get Real-Time Phishing Protections

    Microsoft is working on adding capabilities to its Microsoft Authenticator app to help defeat security breaches enabled by advanced attack techniques, including phishing and man-in-the-middle methods.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.