Anyone working with SSIS and Excel probably had the following issue: you are creating an SSIS package using an Excel file in BIDS/SSDT – which is a 32-bit application – and when you try to run the package, it crashes. This is easily fixed by setting the project property Run64BitRuntime to False. The package now runs in 32-bit mode and uses the 32-bit ACE OLE DB provider which was installed alongside Office (if you installed the 32-bit version at least).
If you want to run the package in a SQL Server job, you’d need to set the job step to use the 32-bit version by selecting the checkbox Use 32-bit runtime in the Execution options in SQL Server 2008 or later, or by using the 32-bit version of DTEXEC in the command line arguments in SQL Server 2005. (For an overview, check out this excellent blog post by Todd McDermid (blog | twitter): Quick Reference: SSIS in 32- and 64-bits)
But what if you want to run the package in 64-bit mode? Easy, you install the 64-bit ACE OLE DB provider which you can download here. Note that there isn’t a 64-bit version available for the 2007 redistributable and that at the time of writing the 2013 version hasn’t been released at all. With the 64-bit provider installed, you can run your packages on a 64-bit server.
If you don’t install the provider, you get the following error when you try to run such a package on a 64-bit SQL Server:
The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. … Description: “Class not registered”
However, if Office is installed in the 32-bit version, the installation of the 64-bit ACE OLE DB provider fails with the following message:
You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products installed.
It basically tells us to uninstall Office before you can proceed with the installation. The funny part is that when I try to install the 32-bit version of the ACE OLE DB provider, I’m greeted with the same message:
Apparently a teensy-weensy part of Office was installed in 64-bit, so I can’t install the 32-bit provider either. Normally you wouldn’t have these problems on a production server, as no Office components are installed. But what if you want to test your SQL Server Agent job on your development machine? Or what if you are reading the Excel file using an OPENROWSET command in SSMS? You need that 64-bit provider badly!
Luckily I came across a solution offered by Lowell in this thread: apparently it works when you install the provider through the command line. All you need to do is add the /passive switch and the installation runs without an issue.
Let’s test if this solves our issue. I created a simple SSIS package reading from an Excel file. Nothing too fancy.
When I deploy it to the SSIS catalog and execute it in 64-bit, I see the package has ran successfully:
What’s interesting is that on my new laptop, which has Office 2013 64-bit installed, I could install the 32-bit provider without an issue. So maybe the Office team fixed the issue in their latest release.
Conclusion
This blog post shows a very simple solution on how to solve a very common problem with SSIS and Excel. I’d like to thank Lowell for pointing me out to this solution in the thread I mentioned earlier.
Update: Installing both the providers on the same machine with Office components installed doesn’t mean your life is issue-free from now on. Apparently this set-up can cause issues in other places. Check out the comments for more details.
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
