Excel connectors and 64-bit

The SSIS Excel connector is a special connector that does not have many configuration options, but does exactly what it should do: read data from an Excel tab and write it back into it. Unless you work in a 64-bit environment.

My current client has a very nice SQL Server environment on a big 64-bit server with 4 processors and a few GB of memory. It all works extremely well and fast, and it is a work environment I would wish for everyone. The problem is that the company also has a lot of people who like to work with Excel, and the information from these Excel sheets has to go to my data warehouse.
My first idea was to make an SSIS package with a nice Excel connector in it, to read and process the sheets. Locally on my 32-bit Windows XP this worked perfectly, but remotely on the 64-bit Windows 2003 the package refused to work under any circumstances.

The problem with a 64-bit environment is that there are apparently no 64-bit drivers for connections to a JetDB database, the format Microsoft uses for its Excel and Access solutions. Without this driver you cannot connect to an Excel sheet or an Access database.
This means that an Excel connector will not work as soon as you deploy the package to a 64-bit environment.
Microsoft has also said it does not plan to release a 64-bit driver for this, so we have a problem.

After some searching and puzzling I found 2 solutions:

Run your SSIS package with Excel connectors under 32-bit executables
A 64-bit SQL Server installation also installs the 32-bit executables, including DTExec (the executable that runs the packages under the hood).
One solution to my problem would be to run my package with this executable.
I normally run my packages with an SQL job, where the job step looks like this:

On the Command Line tab you see exactly what the job step uses under the hood to run my package. These are the parameters that are passed to DTExec, so it knows which package to run and how.
We can use this information: instead of calling my package as a “SQL Server Integration Services Package”, we choose another step type in which we can pass these parameters to the right (32-bit) DTExec.

So choose the right job step type, “Operating System (CmdExec)”, and give the following command as “Command:”:

{geshi xml:lang=“d” lines=“false”}

C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe
/SQL “TESTTest” /SERVER “sql” /MAXCONCURRENT “ -1 “ /CHECKPOINTING OFF /REPORTING E{/geshi}

This way the package is run by the 32-bit DTExec, which can use the 32-bit JetDB driver, so you no longer have problems with your Excel connector.

Excel? Flat file!
Another solution may be faster, but it is often something you cannot do. Take the Excel sheet and convert it to a CSV, so we can use a flat-file connector. This is only possible if you have access to the Excel sheet and your users can work with CSV.