A recent upgrade of a server to 64 bit revealed that there is no 64 bit Excel driver for SSIS, at this stage. With Denali on its way i’m guessing that there wont be one forthcoming either.
This will materialise itself with the following error:
Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager": 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.
In order to overcome this you must run SSIS using the 32 bit runtime. Doing so will depend upon how you call your package, from a SQL Agent job or from the command line.
Running SSIS in 32 bit mode from SQL Agent:
To configure a SQL Agent SSIS package to run as 32 bit you simply have to select ‘Use 32 bit runtime’ from the ‘Execution Options’ tab of the general settings when you are creating the task, or edit the existing task step that you are porting to the 64 bit environment, as shown below:
Running SSIS in 32 bit mode from the Command Line:
Sometimes you have a need to call your SSIS package from the command line instead of SQL Agent, in a default 64 bit installation this is located at the following address: c:\Program Files\Microsoft Sql Server\100\DTS\Binn\DTExec.exe however if you want to load any excel files you will need to call the 32 bit runtime which you can find here: c:\Program Files (x86)\Microsoft Sql Server\100\DTS\Binn\DTExec.exe