How To Write Code
That Doesn't Suck

wry observations from the deep end of the software cesspool

2011-07-14

Reading Excel files into SSIS on a 64 bit OS

Update: Daniel Esser noted the same solution in his post SSIS Goodie #1: Excel + SSIS + 64 bit = DTS_E_OLEDB_EXCEL_NOT_SUPPORTED ? noting a classic code suck for why the Excel Source doesn't work:

It seems that Microsoft implemented an hard if-statement which throws an exception if DTS-Engine runs in 64 bit environment

SSIS has a built in "Excel Source" for reading data out of Excel files, but it has a variety of problems in a 64-bit environment (such as Windows Server 2008), the main one being that the source relies on the Excel Connection Manager, which in turn relies on the old JET engine OLE DB driver, which doesn't run in 64-bit mode. Googling for something like ssis excel 64 bit will give you lots of results and a variety of advice, mostly falling into these three flavors:

I'll let you decide for yourself if any of those solutions are acceptable, but they are not in fact the only solution. Here's how I've done it instead:

  1. download and install the ACE OLE DB driver
  2. create an OLE DB Connection Manager (not an Excel Connection Manager) using that driver and referencing the Excel file
  3. create an OLE DB Source (not an Excel Source) using that connection manager

This approach seems to be working fine and avoids most of the drawbacks of any of the other three approaches. Here's a screen shot of the Connection Manager Connect settings.

And the All tab, here the Extended Properties are important, Excel 8.0 covers older (.XLS) files, Excel 12.0 is used for newer (.XLSX) files. HDR indicates whether the first row contains column names, my spreadsheet didn't but yours may.

2 comments:

Alexander Schneider said...

Hi Ken,
my admin installed "Microsoft Access Database Engine 2010 Redistributable" on my 64-bit machine. But I can't see/select "Microsoft Office 12.0 Access..." when I create an OLE DB connection manager. What could be the problem? Any idea?
Thanks Alex

Ken said...

Sorry Alex, I don't have any good ideas offhand, sounds like it's probably a permissions issue. First step I'd try is getting the admin to go through the steps to set up an ODBC data source and watch to see if the driver is visible for them.

Post a Comment