I am attempting to execute a package in BIDS (actually Visual Studio 2008) and getting this error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. I'm running the 64-bit version of Windows 7 with the 64-bit version of SQL Server 2008 Developer Edition installed. The package accesses an Excel 2007 file (*.xlsx file), and that's where the error occurs. The database I'm attempting to populate is the default instance of SQL Server on the local machine. Probably needless to say, the project and its packages have been working fine for months in 32-bit Vista with Office 2007 installed on the local machine. So far I have: Made sure that the project's Run64BitRuntime setting is False Installed the Microsoft Access Database Engine 2010 Redistributable 64-bit version, even though I have the 64-bit version of Office 2010 installed on this machine. Read all of Todd McDermid's blog posts about the issue that I can find and (I think) tried everything that seemed to apply Attempted to run the package with the 32-bit version of dtexec.exe and got the same error message Went into the registry and verified that Microsoft.ACE.OLEDB.12.0 is correctly installed, as near as I can tell (the ClassID seems to be fine, and the GUID is in place) Looked for other bitness settings in the project and its packages Created a new SSIS project with a single package and a single Data Flow task that uses an Excel Data Source to the same file, and got the same error. Pulled out copious volumes of hair What am I missing? Is this just not possible to do? Did I make a huge mistake going to 64-bit Windows 7? Thanks!!! Don

Microsoft doesnt have any oledb provider of 64bit Excel but you should be able to execute ur package if you do: A) to run from Visual Studio: For solution Properties>> Debugging>> Use64bitRuntime set as False. B) to run from job.. Use 32 bit version of DTEXEC.EXEwhich should be there in x86 directory under sql server and then use cmdline Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/

He's already done that, Rahul... Hi Don. Just to give you hope, I run exactly the config you're trying to get set up: Win7x64, SQL 08 Dev, Office 2010 x64. Didn't have a problem at all. Have you read Douglas Laudenschlager's Excel posts? He talks mostly about reading and writing, but there are some bits about connection strings in there too that might be worth trying. OH - wait a minute. It's looking for an office 2007 driver, not 2010. You'll need to look at the properties of the connection manager and change them to refer to "14" instead of "12". But I don't think it's that simple... I saw something somewhere, probably on the SSIS Team blog... damn. Can't find it. But that's it. Okay... maybe not. Check your connection string against this: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Test.xlsx;Extended Properties="EXCEL 12.0;HDR=YES"; I haven't ever run this outside of BIDS, so let me try that to see if I have the same problem. UPDATE: No - I can get the package to fail reliably from a SQL Agent if I run it in 64-bit mode, and succeed reliably when I turn on 32-bit mode. Talk to me now on

Hi Todd, Thanks for the response! My head is spinning going through all this stuff... I hadn't seen the posts from Douglas Laudenschlager. Very interesting stuff. According to the latest post in that series, my connection string should look like this: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<long path>\ImportData.xlsx;Extended Properties="EXCEL 14.0;HDR=YES"; But when I press enter to save the change, it changes the string to this: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<long path>\ImportData.xlsx;Extended Properties=Excel 8.0; "EXCEL 14.0;HDR=YES"; Why would it add the Excel 8.0??? And then when I run the package I get the same error message about Microsoft.ACE.OLEDB.12.0 not being registered. So that seems to be a dead end. Curse Microsoft!!! Another thing I tried is to install the 32-bit version of the Access Database Engine 2010, but it won't let me since I have the 64-bit version of Office 2010. Okay, so does this mean I'm totally hosed? It would seem that to do any SSIS development I will have to go back to the 32-bit version of Windows 7. Right? Or maybe the 32-bit version of Office 2010? Neither are desirable options. So maybe I can't use SSIS for this project. Despairingly, Don

You should be able to - as I said, I've got it working and I didn't even have to "try" to shoehorn anything. Ah - I just reviewed my installed programs, and I don't have the "Access database engine 2010" - I have the "Access database engine 2007". I think that explains some things. First, it's quite true that you don't have the v12 version of ACE installed - you have v14. So the error message is completely correct. Second, even though you attempt to change the version to 14, it can't see the driver at all. Why? Because (as mentioned in Doug's postings) the Office drivers are 32-bit only OR 64-bit only - you can't install them side by side. So since you've installed the 64-bit driver, the 32-bit BIDS can't see the driver at all. I think that it's throwing the "8" in there in an attempt to downgrade gracefully. So - here's my advice, which may not work... :) Attempt to install the 2007 Access database engine. I'm not entirely sure where I got that "bit" - but this may help. That driver will be 32-bit, which should be visible in BIDS as a v12 provider. If that works at design time, you'll have to configure your runtime execution as 32-bit as well (that RunAs64BitRuntime only goes so far). When you move your package to production, you'll have to install the same bits on your production box, and ensure it executes in 32-bit process space. The only problem with this possible "fix" is if you are intending to use 2010-specific features of Office/Excel. If you are intending to do so, I would contact the SSIS Team to help you work through this scenario. It's something that I think they need to provide guidance on, because you will definitely not be the last person to try to run SSIS packages against a 64-bit Office installation. I can get you in touch with the proper people if you need that. Regardless - if you find a solution, please let me know what it is! Talk to me now on

Hi Don, Since you installed 64-bit ACE driver, why did you set Run64BitRuntime to fase? This will have your BIDS run your SSIS package in 32-bit runtime and looks for 32-bit ACE driver but what you installed was a 64-bit driver. However with only 64-bit driver installed, you could not design your SSIS package with ACE driver since BIDS designer is 32-bit. My suggestion to you is to design your SSIS package in a 32-bit environment with 32-bit ACE and 32-bit Office 2010 but you can run it on a 64-bit environment with 64-bit ACE driver. If you want to design your SSIS package on your Windows 7 computer but run it with 64-bit runtime on another computer. You can uninstall 64-bit Office 2010 from your windows 7, install a 32-bit one, then install 32-bit ACE driver, when you are debugging your SSIS package with BIDS, please make sure that you set Run64BitRuntime to False. With this method, you can design, debugging and test your SSIS package on your machine. If you want to run it in a 64-bit runtime, you just need to deploy it to a 64-bit server with 64-bit ACE driver. Besides of this, there is nothing special compared to a normal SSIS package deployment.Please remember to mark the replies as answers if they help and unmark them if they provide no help

Hi Charles, I listed all the stuff I tried, not the group of settings that I was trying to make work. And toggling Run64BitRuntime was one of many things I tried in order to see how I might get this to work. The production environment doesn't exist and isn't an issue here. I am designing and running the package in my development environment, and that's the only place it needs to run. It is part of early application development in which I need to repeatedly refresh the data in the test and analysis database. It won't be necessary later when the app goes live, some months from now. So I'm stuck in a 64-bit world, unless I tear down my whole development environment and revert to 32-bit, not something I'm anxious to do for just a couple of packages. And, as you've said, I've now realized that I can neither design this package (or at least the Excel source) nor execute it in BIDS. When I first posted here, I was just trying to get it to execute. But if I understand you correctly, the only problem here is that I'm using 64-bit Office 2010? No...I think you're saying that if I go with 32-bit Office 2010 I can design it but not run it? So I can't both design and run it on a single machine configured as I have it? Thanks, Don

Hi Todd, What's weird though is that I DO have Microsoft.ACE.OLEDB.12.0 installed. At least, that class is in the registry. But in tracing through things, that class ID has its InprocServer32 setting set to the Office 14 version of ACEOLEDB.DLL. So things seem to be a bit schizophrenic in the registry. But there is no Microsoft.ACE.OLEDB.14.0 class ID, so maybe that is why it is trying to downgrade versions? Just a guess and probably doesn't matter though. I can't have both the 32 and 64-bit versions of the database engine installed, right? So I'd need to uninstall 64-bit Access? I'm guessing not; I'd have to uninstall the whole Office suite, right? Let me play with this a bit, and then I may take you up on your offer to put me in touch with the Right People. Thanks!!! Don

TBH - I haven't played with it enough myself, so I'm not sure I can reliably inform you of whether there should be an "ACE 14" or not. And yes - all parts of Office 2010 have to match bitness AFAIK. To comment on your reply to Charles, you can definitely "changegrade" to the 32-bit version of Office 2010 and get a complete design and execute environment on your 64-bit Win7 OS. Design-time will be fine because it's expecting the 32-bit version. Interactive runtime will be fine if you set the Run64BitRuntime to false. "Real" runtime (SQL Agent, etc...) will be fine if you use the "run in 32-bit mode" in the IS job step of Agent, or use the DTExec sitting in the Program Files (x86) folder tree. But really, there needs to be a full development "story" for a 64-bit installation of Office (or just the driver) on a 64-bit OS. I'm not too sure how much pressure the IS sub-team of SQL Server can place on the Office team... but it sure would be nice to be able to install 32-bit and 64-bit drivers side-by-side... Talk to me now on

Hi Don, I think that Todd fully explain what I want to say. :) Please let us know if you have any further questions or concerns.Please remember to mark the replies as answers if they help and unmark them if they provide no help

Todd or Charles, I'm desperately trying to find a solution to this same problem. According to Charles Wang's response above, it sounds like I should be able to run my SSIS package on a 64 bit machine as long as I have the "Office 2010 Redistributable Connectivity Components" drivers installed. But I'm still getting this message: Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. My question is, is it even possible for an SSIS package using the Excel Connection Manager 2010 ACE Driver to work on a 64 bit machine since it was developed on a 32 bit BIDS enivonrment? Again, I have the 64 bit ACE drivers loaded on the server I'm deploying to, but for some reason, I still get the message above.

You may want to read Hrvoje Piasevoli's post for more details - Importing data from 64-bit Excel in SSIS. It is possible for SSIS to work with Excel on a 64-bit machine. The development environment is irrelevant. Talk to me now on

It is possible for SSIS to work with Excel on a 64-bit machine. The development environment is irrelevant. Thanks Todd, I actually read that article, and it was very helpful, however it only speaks of a "workaround" like using the import/export wizard or OpenRowSet. I'd like to use the Excel Connection Manager from within SSIS if possible. So you've confirmed that its possible to run it on a 64 bit machine using the new 2010 Excel ACE provider. And it sounds like the only prerequisite is to have the "Office 2010 Redistributable Connectivity Components" installed right? Is there anything else I might be missing? Why would it still be telling me that "The Excel Connection Manager is not supported in the 64-bit version of SSIS"? Here's my Connection String. I'm using dtexec to run it: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\servername\files\CapAccruals.xlsx;Extended Properties="EXCEL 12.0;HDR=YES";

Have you read Douglas Laudenschlager's blog? It has tons of good info on Excel and SSIS - some on 64-bitness. The only reason it should complain to you about being unable to connect to Excel is if the bitness is mismatched between your SSIS runtime and Excel. Have you tried running DTExec's 32-bit version to see if that works? (If it does, that would indicate the 32-bit version of Excel components are installed.) Talk to me now on

Thanks Todd...I will definately check out that blog. I'm just glad you were able to confirm that it "should" work in a 64 bit environment because I was ready to give up on it. Now its just a matter of figuring out what I'm missing, I'm sure its something simple. I will definately post back once I find the issue. Again, thanks for the feedback...now I have a renewed determination to figure this out. Thanks again.