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, and then go to the Debugging tab (under “Configuration Properties”). One of the properties you can configure is “Run64BitRuntime”.

When this property is set to “True” and you run a package in this project from within SSDT, the package is executed in 64-bit mode. When it is set to “False” and you run a package, it is executed in 32-bit mode. This is an important feature, because it allows you to test your package on your dev box the way it is going to be executed in production, even if your dev box is not configured in the same way, and even if your development environment (SSDT) is a 32-bit application.

The problem now is that you’re trying to run the package in 64-bit mode, but you only have the 32-bit version of the ACE OLE DB provider on your machine. This is the same problem as before, but from the other direction. If you change the “Run64BitRuntime” property to “False”, you will be able to run the package successfully.

That’s great, but it’s not good enough. As I mentioned before, if your production environment is 64-bit, then you should test your package on a 64-bit environment. Currently you know that the package runs successfully on a 32-bit environment, but it doesn’t necessarily mean it will run smoothly on a 64-bit environment. So how can you test your package on a 64-bit environment?

One way is to deploy the package to a 64-bit machine (it can be your dev box as well) and run the package in 64-bit mode (not from SSDT). This method will ensure you that your package is running (or not), but you won’t have any debugging capabilities like you have in SSDT. So how can we run the package in 64-bit mode from within SSDT? The answer is simple. Now that we are done with designing the package, we don’t need the 32-bit version of the ACE OLE DB provider anymore (at least for now), so we can switch to the 64-bit version of the provider again (uninstall and install, remember?).

As soon as you have the 64-bit version of the provider installed, you should change the “Run64BitRuntime” property back to “True”, and you should be good to go. Well, almost… Now you get the following package validation error message:

Now what?

This is tricky. Whenever a package is executed, it first goes through a validation phase before the actual execution begins. The goal of this validation is to verify that everything is configured properly before attempting to execute the package in order to avoid a failure at runtime. Since this validation occurs before the package is executed, it is performed by SSDT, which runs in 32-bit mode, regardless of the value of “Run64BitRuntime”, which only affects the environment of the runtime execution. Since you currently only have the 64-bit version of the ACE OLE DB provider and not the 32-bit version, SSDT can’t use the provider, and validation fails.

If you think about it, you’re trapped. If you install the 32-bit version of the ACE OLE DB provider, you can pass validation, but you won’t be able to run the package in 64-bit mode. On the other hand, if you install the 64-bit version of the provider, then you can (theoretically) run the package in 64-bit mode, but you can’t get to that point, because you fail validation…

The way to work around this problem is to use the “DelayValidation” property of the Data Flow Task. The default value of this property is “False”, which means the task is validated before the package begins its execution (as described above). If you change this property to “True”, the task will be validated only at runtime, just before it is executed. Since validation now occurs at runtime, it is performed by the process that runs the package, which is controlled by the “Run64BitRuntime” property. So now this validation occurs in a 64-bit environment, and it can use the 64-bit provider.

Victory at last!

The “DelayValidation” property has many other uses. Also, we haven’t covered the execution of the package outside of SSDT (e.g. from a SQL Server Agent job) in a 32-bit environment vs. a 64-bit environment. These topics are beyond the scope of this post.

#SSIS #SSDT #development