Windows Server 2008 SP2 Enterprise Edition 64-bit
SQL Server 2008 SP3 Standard Edition 64-bit
Previously had SQL Server 2005 Developer Edition 32-bit Integration Services. That has now been uninstalled.
Our ASP.NET applications need to execute SSIS packages in-process by using the ManagedDTS.dll assembly. We are developing SSIS packages in BIDS 2008 (as opposed to 2005) so that we can import / export Excel 2007-2010 files. Our ASP.NET applications are being developed in Visual Studio .NET 2010 compiling to .NET Framework 4 code. I am referencing the following version of ManagedDTS.dll on my 32-bit Windows 7 development machine.
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
We deploy our applications to a Windows 2008 64-bit server. IIS 7 on that server is used to toggle running our applications in 32-bit or 64-bit mode.
When we run the application in 64-bit mode and execute the SSIS package, the package does parse correctly. However, it gives a runtime error which tells us that the Excel Connection Manager is not supported in the 64-bit version of SSIS. That is not the problem I am needing to fix right now, though.
The real problem lies in executing the package while the application is running in 32-bit mode. Execution throws an unhandled exception which gives this error message:
The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.
This error message makes me think that somehow the package is being parsed by the SSIS 2005 xml parser. That shouldn't be possible since I've fully uninstalled SSIS 2005.
Interesting bit of information
So I created a brand new, blank package and replaced the real package with it. It worked correctly. I have now determined that the error manifests itself only when a Connection Manager is present in the package. This supports my theory that somehow in 32-bit mode the packages are being parsed by a rogue SSIS 2005 dll, and the Connection Manager section in 2008 packages is sufficiently different xml from their 2005 counterparts. Just my theory.
Nonstarter for me
Probably some people will suggest running the package from the command line via the 32-bit version of DTExec. While that would certainly work (I've confirmed it), that is not a solution for me. I need run the package from the code in-process so that the application can easily get and set global variables defined within the package. Running the package from the command line, to me, seems like a workaround for a problem that should not exist.