Error loading SSIS packages using 32-bit dtexec on x64 machine

Question

So, I have Integration Services packages that need to work with Excel files (shudder) and also need to execute on x64. I have a handle on the whole SSIS x64 vs. 32-bit issue, so I am scheduling these problem-child packages using MS suggested technique of using SQL Agent job step type Operating System / CMDExec, with a command line string that explicitly calls the 32-bit dtexec. So far so good.

Here's the issue: the packages still fail to load, and complain about failing to load not the Excel bits, but instead my handy Log Provider that logs to SQL Server. This is the error message, edited for object names:

... Started: 2:33:01 PM
Error: 2009-07-24 14:33:06.51
Code: 0xC0010018
Source:
Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">My_LogCon</DTS:Property><DTS:Property DTS:Name="DTSID">{86320FE6-AEFD-4A58-9277-84685B9B9" from node "DTS:ConnectionManager".
End Error Could not load package "c:\folder\mypkg.dtsx" because of error 0xC0010014.
Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. ... Process Exit Code 5. The step failed.

The packages run perfectly in 32-bit Visual Studio/debug. Anyone seen this type of thing?

Answer 1

I do not think this is Excel related. Your error message clearly states:

Could not load package "c:\folder\mypkg.dtsx"

More detailed error message might help. Also curious to know, are there a lot of processes after the Excel file loads? I am faced with the same scenario and it is shame having to run the whole process in 32 bit.

I might have a work around that will allow you to use Excel files as source, but still configure it to run as a SSIS job, not CmdExec. Will share details once I test this.

Check this link. There seems to be a different reason for your issue.

Raj