Let’s say you want to load data from an Excel 2003 (.xls) file into a database using SSIS:
If you’re running on a 64-bit machine, you’ll get this:
Plus this error:
[PASS Regional Mentor Spreadsheet [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Not good. There is no 64-bit JET driver, and the JET driver is used by the Excel ConnectionManager. Phooey. Sad face. Bummer.
So how do you get around this? One quick and easy way is to execute this package using the 32-bit debugger. To change the default setting, right-click the Project in Solution Explorer and click Properties:
When the Properties dialog displays, click the Debugging page from the list on the left and then change the Run64BitRuntime property to False:
When you execute the package in the debugger (or in the runtime), it will now succeed:
:{> Andy
Andy great post, also note that when running the package remotely via a 3rd party job scheduler, etc. If your running on a 64bit instance of SSIS you can call the 32 version of dtexec.
Explained here with some good detail. This has bitten us before.
http://msdn.microsoft.com/en-us/library/ms162810.aspx
But it will (likely) fail when you run it outside BIDS – gotta run the right DTExec for that. (http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html) (You don’t mind me hijacking your blog, do you Andy? 🙂 )
Second – Office 14 (aka 2010) WILL HAVE 64-bit DRIVERS! (So said Douglas Laudenschlager… http://dougbert.com/blogs/dougbert/archive/2009/08/28/64-bit-excel-driver-in-microsoft-office-14.aspx) Hooray! Now if they can just get rid of the insanity of the Excel provider’s "type inferring" behaviour…
Thanks Keith and Todd!
Have you guys been reading my posts scheduled for the future?
:{> Andy
Also, as I understand it, with Office 2010 you can only have the 64 bit or the 32 bit driver on a machine, not both. So this could be problematic if you wanted to design a package on the server in BIDS (using 32 bit driver) and then deploy to production on the same server using the 64 bit driver. If anyone has heard something different I’d love to hear about it.
Drew.
Great info in the post and comments (Hooray for 64-Bit Excel driver!)
Here are some notes that I collected while dealing with this and related 64-bit issues (with the links from these comments added in. Thanks guys!)
64 Bit SQL Server SSIS Packages with Excel and Access Data
http://www.amosfivesix.com/sql/34-64-bit-sql-server-ssis-packages-with-excel-and-access-data
More SQL Server 64-Bit Issues
http://www.amosfivesix.com/sql/35-more-sql-server-64-bit-issues
THAT’S where that obscure "kicking my butt trying to do a simple access db extract" setting is! Thanks Andy! Say hello to the Dan-man for me while you’re at it..
Thank you for the post. Great and very useful info
Thank you! Simple, straightforward and it worked.
Exactly what I was looking for. Thanks!
Muchas graciss. Justo lo que estaba buscando.
De nada, señor.
:{>
Hey..
Its work
Thanks
Thanks!!!!!!!