Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!

SSIS Snack: SSIS, Excel, and a 64-bit OS

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

Published Tuesday, January 19, 2010 8:00 AM by andyleonard

Filed under: SSIS, x64, SSIS Snack, Excel

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

	Keith Mescha said: 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 January 19, 2010 12:05 PM
	Todd McDermid said: 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... January 19, 2010 12:16 PM
	andyleonard said: Thanks Keith and Todd! Have you guys been reading my posts scheduled for the future? :{> Andy January 19, 2010 12:23 PM
	DrewskyJones said: 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. January 19, 2010 2:47 PM
	AmosFiveSix said: 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 January 20, 2010 8:32 AM
	Jerry Dearbeck said: 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.. April 1, 2010 3:21 PM
	Thejeswini said: Thank you for the post. Great and very useful info January 20, 2011 12:27 AM
	Miki Breakwell said: Thank you! Simple, straightforward and it worked. June 6, 2011 5:01 AM
	Kristina Moody said: Exactly what I was looking for. Thanks! April 12, 2012 5:52 PM
	Jorge Rivera said: Muchas graciss. Justo lo que estaba buscando. November 14, 2012 5:43 PM
	andyleonard said: De nada, señor. :{> November 14, 2012 5:50 PM