Contact us

Madeira Data Solutions

Your Data, Our Solutions

How to Load Data from Excel in SSIS – 32-bit vs. 64-bit

Written By: Guy Glantser 25/02/2014

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:


Excel Source Editor - SQL Command 


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.



47 responses to “How to Load Data from Excel in SSIS – 32-bit vs. 64-bit”

  1. Jaya says:

    Great post.. Thanks a lot sir!

  2. richu thakur says:

    Thank you! Each and every step is very well explained.

  3. Wassem Sabri says:

    Awesome, It is very clear now. Thank you so much.

  4. Ann Ominous says:

    Oh my god – what a Rube Goldberg solution! And to be clear, that’s not aimed at you, it’s aimed at Microsoft. This is a horrendous amount of effort and inefficiency, and it’s almost (almost) unthinkable that they wouldn’t have done something to address this by now. It’s 2017, can they really not either produce a 64 bit version of SSDT, or at least build in some way to make the 64 bit provider “impersonate” the 32 bit driver?

    That said, my hats off to you, not only is it impressive that you were able to piece all of this together, but you did a phenomenal job of clearly explaining what the problem is, and the steps one has to follow to “resolve” it.

    Thanks for a GREAT article!

  5. Kaustubh says:

    An observation. For all the people who have 32 bit office product installed, Go to Project Properties >> Configuration Properties >> Debugging , Set Run64BitRuntime = False

  6. Apoorv Jain Deshmukh says:


    I am using SSIS and excel 2016 version on a server. I followed the same steps but after uninstalling the 64 bit version the 32 bit version is not installing. I am installing the Microsoft Access Database Engine 2016 Redistributable, should I go for the 2010 version only?

  7. Ryan Boal says:

    Excellent advice! This completely validated what I suspected, i.e., that the import tool was only available in 32-bit version. I verified the order of Tool paths, and the 64-bit was always ahead of the 32-bit, but there were no apps in the 64-bit Binn folder. Thank you again!

  8. AD says:

    When I use Excel.xlsx as the source file it runs fine from Visual Studio installed on my desktop, but when I import the package to the Integrations Services Catalog in SQL Server 2012 sp1 64 bit, it won’t execute. It will only execute if the source file is using Excel.xls

    • Guy Glantser says:


      You are probably using the 32-bit driver on your desktop, which is not installed on the SSIS catalog machine. Follow the post, step-by-step, it explains exactly this scenario.

  9. Zara says:

    Brilliant!!! Thank you

  10. Tassabih says:

    That’s just amazing!!! thank you so much. I’ve been going in circle for a long time now because of this!!! this is really helpful!

  11. Nick B says:

    Yes, brilliant post.
    It’s just a shame that we have to jump through these hoops at all. Its about time Microsoft got their own applications (Windows, Office, VS, SQLServer) to run together a bit more seamlessly. If they can’t do that, what’s the point of using tools from a single provider … we might as well use “best in class”, and in that case, they probably would not all be Microsoft.

  12. ggg says:

    That was a good explanation. I uninstalled 64 bit and reinstalled 32 bit and it worked.

  13. craig v says:

    The most concise explanation and solution I have found! Thank you!

  14. udhaya says:

    excellent write up, exactly the problems which are stated are occurred and solved in the same order 🙂 , good work done, thank you for the help

  15. Hassan Javed Cheema says:

    Excellent i got rid of the shit that i was facing. Thankssss..

  16. Amine GO says:

    good post thanks

  17. Prashanth says:

    Rightly Put from start to the end Glanster.
    I am only a beginner in IT and it is my Day 2 with SSDT 2012. But, I find this blog really understandable and meaningful.

    Thank you for your time to piece this article in the most perfect way.

  18. Kunal says:

    My package is running absolutely fine when executed from SSIS. Also, I have created job through SQL Server Agent. When I run job, by Right click–> Run Job, Job shows success message but data is not loaded into DB table. Can you please tell the reason, it will be very helpful?

    • Guy Glantser says:

      Hi Kunal,

      I’m sorry for the late response. I found out just now that I have some old responses that I missed.
      For what it’s worth, I will try to answer your question here…

      There could be several reasons why data is not loaded into the table.
      The first option is that the package indeed ran successfully, but there was simply no data to load. You need to check the source and verify that there is indeed data that should be loaded in to the table.
      The second reason is that something failed in the package, but the package reported a success. There are several properties in the package that you can tweak in order to control that behavior. One of them, for example, is the “FailPackageOnFailure” property. If you set this property for a task to False, and the task fails, then the package itself doesn’t fail.
      Another reason is that the package failed, but the job step reported success. You can control the behavior of each step in the “Advanced” tab.
      You need to use SSIS logging in order to troubleshoot what exactly happens during package execution.

  19. Jonathan Currie says:

    Guy, great post! Found this article to be a excellent solution to my issue.


  20. Asrar says:

    Well done and Thank you, saved me lots of time

  21. kshah says:

    excellent article!

  22. George Benson says:

    I was writing this explanation up for my Sys Admins and found this article while doing some research. Now I don’t have to write anything up…..excellently done

  23. Bala says:

    Nice Post, Thank you!

  24. Sainadh says:

    Excellent Post but need some extention for this like, i deployed this package in integration services and created a job to run this package but it was not executing can you help me in this issue.?

  25. Joe says:

    Thank you!

    This was the most straight forward and best explanation I have found for this problem! I am running 32 bit Office on a 64 bit machine and had installed ‘Access Database Engine 2010 Redistributable x64’ as I was lead to believe it needed to match the OS.

    I uninstalled and ran the 32 bit version instead and imported data first attempt!

  26. Jupi says:

    Great Post! I have been fighting with this excel problem for weeks, this worked!! Thank you !

  27. Madhu says:

    Excellent Post!!! It was really helpful to get rid of my issues.
    Thank you so much!!!

  28. Joe says:

    Great! This post is very nice!

  29. Roopesh says:

    Superb article!!. Reading this was like attending a classroom training. Thanks Guy.

  30. Eran says:

    Great post and great trick Guy.
    relevant even today

  31. Keivan says:

    Thank you Guy .

  32. Dadang says:

    Brillian… great explanation… trully case by case and step by step… Its answer everything… many thanks…

  33. Mariusz Godziszewski says:

    Thanks so much !
    Why the settings are so unclear and complicated today :/


  34. Roger says:

    Thanks so much, Guy! This was exactly what I needed! Your very careful and thorough explanation of how to manage an intricate process was a credit to yourself and all technicians who strive to help others. I’ll try to do as well for the next person!

  35. Madhavi says:

    Thanks Guy, It solved my problem.

  36. David says:

    Thanks!! This really helps!!

  37. Chayan Saluja says:

    Awesome post!! It solved my problem.. More than that the way you have explained it is amazing!

  38. Rich says:

    Fantastic write up. You saved my day. I appreciate it!

  39. Hila says:

    Excellent Post!!!
    I’ve used it today to import data from Excel and it solved everything…

    Thanks! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *