This post is more of a note to myself in order to bookmark the Data Connectivity Tools for, in my case, SQL Server installs.
If, for example, you wish to load Excel files with SSIS, your server will require the appropriate drivers, one method to handle this would be to load office onto the server, this is not recommended though for security reasons as it provides an unneeded possible vulnerability, however low and also just means that you have another set of programs to patch as updates are released.
In order to get around this, simply load the free drivers from Microsoft, which are available for download from this address:
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