SQL Server Agent: Error When Running a DTSX Package to load Excel

When deploying a project onto a 64 bit Windows Server machine, I noticed an issue with scheduling SQL Server Agent to run a DTSX package to load an Excel file. The error is a bit like this:

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered"

I could run the package directly using SQL Server Management Studio without any problems, so it's obvious that the issue is being introduced by SQL Server Agent. After rooting around on the net looking for the answer, it seems that the agent is trying to run the package in 64 bit mode.

There are no 64 bit Excel drivers, so the package fails. For information on the impact that 64 bit architecture has on SSIS, refer to this blog post by Deniz Arkan, the SQL Server Integration Services Program Manager at Microsoft, and this Microsoft article on the subject.

Many forum posts suggest that the package should be run via the Agent using the 32 bit version of DTEXEC rather than the 64 bit version.

By default, a 64-bit computer that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt. The 32-bit version runs because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. (Typically, the 32-bit directory path is :\Program Files(x86)\Microsoft SQL Server\90\DTS\Binn, while the 64-bit directory path is :\Program Files\Microsoft SQL Server\90\DTS\Binn.)

So if you have both 32 bit and 64 bit versions installed, you have to explicitly point to the 32 bit version of DTEXEC on the command line.

Alternatively, to run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box. This depends on your having the 32 bit runtime tools installed.

For a step by step description of how to run a package from an Agent job in 32 bit, refer to this Microsoft KB article.

But after all this reading, I bottled it. I can't be bothered having to get the 32 bit version of the runtime installed on the Dev, UAT and Production boxes so I decided to get the files I needed converted into a flat file format. This won't suit everyone as not everybody can get their input files changed to a different format, but for me it's the best route.

Anyway, clearly I am by no means an expert on this subject, but I reckon that Microsoft are trying to discourage people from using 32 bit architecture on 64 bit machines by making this kind of thing difficult to implement, and by not providing 64 bit drivers for their Office products.