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

  • Sync Issues Can Arise for PCs with Poor VPN Connections

    Microsoft this week reminded IT pros that PC connections through virtual private networks can sometimes lead to time synchronization issues, possibly causing reduced functionality for end users.

  • HoloLens 2 Borrows Its Killer Feature from Windows

    Turns out the secret to the HoloLens 2's success has nothing to do with holograms.

  • Microsoft Simplifying VPN Configurations for Its Video Streaming Services

    Microsoft this week announced that it is working on a more simplified way for an organization to leverage local end user Internet connections when accessing Microsoft Stream and Microsoft 365 Live Events video feeds.

  • Microsoft Previews MSIX App Attach for Windows Virtual Desktop

    Microsoft this week indicated in an announcement that the MSIX App Attach capability in the Windows Virtual Desktop service can now be tried via a preview of the Windows 10 Enterprise Multisession operating system.

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.