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 Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

  • Microsoft Nudging Skype for Business Users to Teams

    Microsoft on Thursday announced some perks and prods for Skype for Business unified communications users, with the aim of moving them to the Microsoft Teams collaboration service instead.

  • How To Improve Windows 10's Sound and Video Quality

    Windows 10 comes with built-in tools that can help users get the most out of their sound and video hardware.

  • Microsoft Offers More 'Solorigate' Advice Using Microsoft 365 Defender Tools

    Microsoft issued yet another article with advice on how to use its Microsoft 365 Defender suite of tools to protect against "Solorigate" advanced persistent threat types of attacks in a Thursday announcement.

comments powered by Disqus