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.
- By Eric Johnson
- 06/21/2010
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.
 |
| 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.
 |
| 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.