The package failed to load due to error 0xC0010014

We recently got SQL Server 2008 and deployed an instance on one of our QA servers. Nothing out of the ordinary, till I tried to deploy a 2005 SSIS package on the QA server.

As usual I took SSISDeploymentManifest file, right click and selected Deploy. I was doing a file system deployment, so the first thing out of ordinary that I noticed was, the installer gave me SQL 2008 deployment path; which I assumed was okay as the 2008 installation may have overwritten some registry default value. So I selected the path where I wanted it to be and rest of the process went without a problem.

Now when I go to SQL 2005 to create a job process for the package, it gives me the error

TITLE: SSIS Execution Properties
------------------------------

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.


------------------------------
ADDITIONAL INFORMATION:

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.

It turned out the default SSIS installer launched was for SQL 2008 and it didn't read anything from the manifest to identify which installer it should invoke (like Visual studio does).

The way to deploy SSIS packages in such scenarios is to right click and go to "Open With". The interesting thing here was that both options listed here are : "SQL Server 2005 Integration Services Package Installation Utility". So prima-facie we won't know which one to select. I tried both and it turned out that the second option was the one that is for SQL 2005.

On a bit further investigation, I found that the "Description" on the "Version" tab of "Properties" of dtsinstall.exe is still "SQL Server 2005 Integration Services Package".

Looks like somebody at Microsoft overlooked this step of testing. :)

SSIS: Changing connection string in Data flow dynamically

Recently I was developing an SSIS package where I had to source data values from Oracle and SQL Server. The SSIS package had multiple For..Loop containers to mimic threading structure in SSIS. Since, at design time I couldn't identify which SQL query (the source) would execute in which For..Loop container, I decided to source the connection string from a table in my SQL Server and then assign it to the OLEDB source at runtime.

For package validation purposes, I had an Oracle connection assigned to the OLEDB source at design time.

Everything works fine till here. But when I ran the package, I found that it will throw an error

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name 'MyTable'".

And

[DTS.Pipeline] Error: "component "Alert values from Oracle" (585)" failed validation and returned validation status "VS_ISBROKEN".

After much turmoil I found that for some weird reason the "Initial Catalog" value assigned in the connection string for SQL Server doesn't work as expected. And since the schema assigned to the package user would connect to Master database it couldn't locate the table mentioned.

Solution: Use the absolute table path in the FROM clause like

select column1 from dbname.schema.mytable

And that did the trick.