How to Load Data from Excel in SSIS – 32-bit vs. 64-bit
Let’s say you are developing an SSIS package on your dev box to load data from an Excel file to SQL Server. For this example, let’s assume your dev box is Windows 7 64-bit with SQL Server 2012, and the Excel version is above 2007 (meaning you’re using an “xlsx” file).
So you launch SQL Server Data Tools, you create a new SQL Server Integration Services project, and you drag a Data Flow Task onto the control flow design area. Then you go to the data flow design area and you drag the Excel Source component. You create a new Excel connection manager that points to the Excel file, you choose “Table or View” as the data access mode, and then you want to choose the table or sheet within the Excel file.
But the only item in the list is “No tables or views could be loaded.”, and you also receive an error message that looks like this:
According to the error message, the connection manager failed to connect to the source. So let’s look at the connection manager:
Not much to do here… So what is the problem?
If you ask me, the first problem here is that the error message is not clear and doesn’t provide any useful information about the cause of the problem or how to deal with it. Here is a nice trick to get a better error message from the tool. Go back to the Excel source editor, choose “SQL Command” as the data access mode, and write a query in the “SQL Command Text” box, like this:
Now when you click “OK”, you receive the following error message:
This is better. It’s ugly, but it provides more useful information, and we have some clue about the next step.
So what’s going on?
The Excel connection manager is trying to use the ACE OLE DB provider in order to access the Excel file when the version is above 2007 (xlsx). According to this message, this provider is not registered on your dev box, so it can’t use it, and this is why it fails.
So the next step is to download and install the ACE OLE DB provider. It is included in the “Microsoft Access Database Engine 2010 Redistributable”, which you can download here. When you click “Download”, you are presented with two optional downloads – one for 32-bit runtime and the other for 64-bit runtime.
Since you’re using a 64-bit box, you choose the 64-bit version of the download. It makes sense, right? You download and install the provider, and you try to choose the Excel table within the Excel Source Editor, but you get the same error. What now?
Now is the time to explain what’s going on…
Although your box is 64-bit, you’re using SQL Server Data Tools, which is a 32-bit application. There is no 64-bit version for SSDT, so you don’t have a choice here. By the way, this is true for any Visual Studio application, not just SSDT. When you design your package within SSDT, you’re using a 32-bit process, which can only use 32-bit providers. When you try to choose the table in the Excel file, the connection manager needs to access the 32-bit version of the ACE OLE DB provider, but this provider is not registered on your machine, only the 64-bit version is installed.
Go ahead and download the 32-bit version of the “Microsoft Access Database Engine 2010 Redistributable”. When you try to install it, you will receive the following error message:
Don’t worry, you don’t need to uninstall Office altogether, only the 64-bit version of the “Microsoft Access Database Engine 2010 Redistributable”, which you installed previously. The 64-bit version and the 32-bit version can’t live together on the same host, so you’ll have to uninstall (through “Program and Features”) and install the other one if you wish to switch between them.
Once you finish uninstalling the 64-bit version and installing the 32-bit version of the provider, the problem is solved, and you can finally choose the table within the Excel file. The Excel connection manager is now able to use the ACE OLE DB provider (32-bit version) in order to access the Excel file. Terrific!
Now you can drag an OLE DB Destination, configure it to connect to a SQL Server table, connect between the source and destination, map the columns correctly, and your package is ready.
But when you try to run the package, the Excel Source component fails…
You can watch all the errors in the Progress tab. The error is similar to what we’ve seen before, saying the ACE OLE DB provider is not registered. But haven’t we taken care of that problem already? We installed the 32-bit version of the provider, and we saw that the connection manager was able to access the Excel file. So what is the problem now?
While the designer within SSDT can only work with a 32-bit version provider (being a 32-bit application), you can still choose to run the package from within SSDT in 64-bit mode. Actually, this is the default on a 64-bit machine. How do you do that? On the Project menu, click on your project properties,