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:
- run the package in 32-bit mode
- use the newer ACE OLE DB driver (released with Office 2010), and
- create an SSIS package using the SQL Server Import Wizard (that then can't be edited using BIDS)
- connect indirectly to the Excel file via SQL Server Update: Hrvoje clarifies that he's specifically tackling the situation where you cannot install a driver which is bit-size-compatible with your BIDS package.
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:
- download and install the ACE OLE DB driver
- create an OLE DB Connection Manager (not an Excel Connection Manager) using that driver and referencing the Excel file
- 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.
