I can’t even begin to tell you how many times this error has popped up on me; it’s like the ‘Dread Pirate Roberts’ from the movie ‘Princess Bride’, you never want to meet him! But I have great news, I have a semi-quick fix for the issue. I will give you the ‘down and dirty’ way to fix the problem, because you’re most likely in the middle of a project and need the solution NOW. But, if you’re interested to find out WHY the error occurs, keep reading. There will also be a link at the bottom to a great article by Guy Glantser called “How to Load Data from Excel in SSIS -32 bit vs 64 bit’” and how he fixed the dread error message. His article provided me the solution!
If you encounter this error you’re most likely working in a 64-bit environment (SQL Server, Excel, etc) and using a 32-bit tool like SQL Server Data Tools (SSDT). There is no 64-bit version of SSDT; it doesn’t exist so just get used to it:) By now, you should know that 32-bit and 64-bit do not play well together. That’s probably why Microsoft makes 32-bit application installations the default setting, and why you must go out of your way to load the 64-bit version. Don’t even get me started about setting up Office 365 in 64-bit mode; that’s a rant for another day!
Quick Steps
You’ll need to uninstall the Microsoft Access Database Engine 2010 Redistributable 64-bit version from your machine and download and install the 32-bit version. Click here to get the download. After you have the 32-bit version installed, you’ll need to make a few changes in your SSDT environment.
Under the Project tab of your current project, click the Project properties at the bottom; in my case, the project I’m working on is called Wise Owl SSIS Project. Click on the Project Properties (see screenshot) and change ‘Run64BitRuntime’ to FALSE’.
Last thing to do is go to the Properties box for your Package (in my case it’s called ‘ConditionalSplitLes7’ package), and change the ‘DelayValidation’ to TRUE.
Now run your package and you should start seeing little green checkmarks. Happiness is Little Green Boxes!
How and Why
If you’re like me, I’m not completely satisfied until I understand the reason behind the error and why the fix works. So hang with me a little longer for the details; I hope you’ll leave happier and wiser.
You already know that the problem stems from your 64-bit environment being forced to deal with a 32-bit application, specifically SSDT. Your Excel connection manager wants to use the ACE.OLE DB provider; but that pesky error keeps popping up, the ‘ACE OLE DB provider is not registered’. But remember, your ACE.OLE DB provider is 64-bit AND you’re trying to interface with a 32-bit SSDT. So this is why you had to uninstall the 64-bit and install the 32-bit version of ACE.OLE DB provider. Now you have 32-bit Provider with 32-bit application; that’s why you made the next change: ‘Run64BitRuntime’ to FALSE. When this property is set to FALSE, it allows the package to run in 32-bit; everyone is playing well.
But you still want to run the package in 64-bit, but there are validation issues. Let’s look at what’s happening beneath the covers. According the Guy Glantser’s article, the SSIS package goes through a validation process BEFORE the package execution ever occurs. This preliminary validation is to ensure everything is configured properly before execution.
Since you have the 32-bit provider installed, you can pass the first validation hurdle, but the package will fail upon actual execution when you try to run the package in 64-bit mode. The workaround was to make the last change by switching the ‘Delay Validation’ to TRUE. By selecting TRUE, your task will be validated at run time; and as the package is controlled by the ‘Run64BitRuntime’, your package is executed in 64-bit! See all my little green boxes!
Hopefully we’ve seen the end of the ‘ACE.OLE DB.12.0 Provider not registered’ error. So long Dread Pirate Roberts!
Here is the link to Guy’s article
Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!