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.

Featured

  • RAMBleed Side-Channel Attack Method Disclosed by Researchers

    Academic researchers this week published information about another side-channel attack method, called "RAMBleed," that can expose information from memory chips, including encryption key information.

  • Penguin

    Windows 10 Preview Build 18917 Shows Off New Linux Integration

    Microsoft's latest Windows 10 "fast-ring" preview release is showcasing a coming Delivery Optimization enhancement, along with the ability to try the newly emerged Windows Subsystem for Linux version 2.

  • Customizing Microsoft Office 365

    While the overall look and feel of Office 365 is pretty standard across organizations, there are several ways to personalize it and make it fit better with your company's specific needs.

  • Microsoft 365 Business Tenants Getting Conditional Access and Trouble-Ticket Features

    Microsoft added its conditional access security service to Microsoft 365 Business subscriptions, according to a Wednesday announcement, and it also added new trouble-ticket features for Microsoft 365 administrators.

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.