When you try to run a scheduled job (with SSIS package) that connects to Excel or Access file on 64-bit server you will get errors like following:
Excel Destination 1 1 [15]] Error: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Excel Destination 1" (15) failed validation and returned error code 0xC020801C.
[Connection manager "Excel Connection Manager "] Error: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
This is all due to the fact that SQL Agents tries to run SSIS package using a 64-bit version of dtexec.exe. The utility will try to use 64-bit version of JET/ISAM driver to access Excel/Access files and fail since they do not exist.
Solution is to run 32-bit version of dtexec.exe. You can do that by manually creating the command that references the right utility. First, copy the dtexec parameters of the job step that you created (see picture):
Then replace it with a new Opertaing System (CmdExec) job step and assemble the command line from reference to the 32-bit dtutil (typically C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe) and copied parameters.
No comments:
Post a Comment