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’.

Run64BitRuntime

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.

DelayValidation

Now run your package and you should start seeing little green checkmarks.  Happiness is Little Green Boxes!

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!

Before I sat down to write this post, I reviewed the Lynda.Com website to refresh my memory of courses taken and courses I want to take.  Forty minutes passed before I looked up from my screen; so many great courses, so little time!  Today we’ll break down the plethora of Lynda.Com training options over several blog posts: Part 1-SQL Server and T-SQL, Part 2-Excel 2013, and Part 3-Microsoft Business Intelligence/Excel 2013 Power BI.  Other Lynda.Com courses on ‘building your brand’: Branding Basics, LinkedIn, and WordPress for Blogging will be examined later in the overall series. For my previous post on Lynda.Com basics and free trial information, click here to read. So, if you’re ready, join me in exploring Lynda.Com courses on SQL Server.

SQL Server

SQL Server is the backbone of the Microsoft BI stack: SSIS (SQL Server Integration Services), SSAS (SQL Server Analysis Services [Multi-dimensional and Tabular flavors]), and SSRS (SQL Server Reporting Services); with everything ‘talking’ to each other via T-SQL (Transact SQL) language and DAX (Data Analysis Expression) language for Tabular and PowerPivot in Excel 2013. To help you build a solid foundation, I‘ve listed several Lynda.Com courses with their time length and subject matter. You don’t have to follow my order, but it will probably help you from getting lost and ‘falling down a rabbit hole’.

At the time of this writing, it’s hard to find the SQL Server 2012 Developer Edition for $59, but you can still purchase the SQL Server 2014 Developer version for around the same price. So purchase SQL 2014 Developer Edition if you don’t already have a SQL Server instance, so you can follow along with the Lynda.Com SQL Server 2012 course without any problems.

Installing SQL Server

The first course to watch is Installing SQL Server 2012; it’s a three hour long course. Not that you’ll be able to get through it in three hours, as you will want to stop and rewind the video, take a few notes, and actually practice the examples on your own SQL Server Developer Edition. So it will be helpful to have an extra monitor for these classes; one to view the video and another screen to work with your SQL Server application. If you have not purchased your SQL Server Developer Edition for 2012 or 2014, this would be a great way to watch the video and install your software at the same time!

Installing SQL Server 2012 teaches you how to install and maintain your SQL Server application, either from the GUI (Graphical User Interface-it’s like an installation wizard) or from command prompts. This course also shows you how to configure and enable the different components of SQL Server, and how to connect to databases.

The Language that Makes SQL Server Run

The second course I suggest is Introduction to Transact-SQL; it’s over fours long but you’ll need every minute of it.  This course is a good introduction to T-SQL, but the best way to learn T-SQL is from Itzik Ben-Gan books; Ben-Gan is a T-SQL guru to me.  In an upcoming blog I will talk about my favorite training books and authors, but if you can’t wait until then, I recommend Ben-Gan’s book ”Microsoft SQL Server 2012 T-SQL Fundamentals”.

With the Lynda.Com course on Transact SQL, you will learn how to use the SQL Server Management Studio (SSMS), basic command statements like SELECT, inner and outer joins, and more.  Another Lynda.Com course, Querying Microsoft SQL Server 2012, will build on the previous T-SQL course.

Querying Microsoft SQL Server 2012 is over five hours, and includes a history of SQL and the relational data model theory. From here you’ll build your skills to include working with special data types like characters, integers, time and date. You’ll be exposed to advanced topics like subqueries, common table expressions, and query performance.

If you have the SQL Server 2014 Developer Edition, this next course SQL Essential Training (3 ½ hours) has been updated for SQL Server 2014. The course provides a good understanding of SQL terminology, writing basic queries, sorting and filtering, along with updating a table with triggers. Creating views is also included in this course.  These courses will give you a good overlay of T-SQL, but I still recommend you buy the Ben-Gan book; yes, the book is just that good!

So What’s Next?

If you have completed the above courses, you now have a basic understanding of the SQL Server application and know the basic T-SQL commands, but these two don’t paint the full picture; you need to understand the concepts of a relational database, so I recommend another Lynda.Com course, Relational Database Fundamentals. To be a good developer or report writer, you must know about relational databases and, well, ‘relationships’!  SQL Server Reporting Services in Depth is an advanced course on Lynda.Com, but it’s a good way to show how to build static reports from a relational database.

Final Thoughts

I’ve provided you several excellent training courses through Lynda.Com, but they might seem overwhelming. Where do you find the time? I remind you of the old saying, “How do you eat an elephant? One bite at a time”. This is a good time to remember this saying.  You will not become technical overnight, but you WILL become technical; you just need to remain steady in your studies.
We’ll continue exploring other great courses on Lynda.Com for the next few posts, so stay tuned.  Until then!

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!