SQLErudition.com

SQL Agent and SSIS packages have multiple failure codes but DTS_E_OLEDBERROR or 0x80040E55 don’t tell you much about the cause.

One of my jobs failed sporadically with the following error.

Message
Code: 0xC0202009
Source: [29]
Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred.
Error code: 0x80040E55.
An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80040E55
Description: "Column does not exist.".
End Error

Error: 2016-11-30 11:20:03.11
Code: 0xC0047038
Source: SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on returned error code 0xC0202009.
The component returned a failure code when 
the pipeline engine called PrimeOutput().
The meaning of the failure code is defined 
by the component, but the error is fatal and 
the pipeline stopped executing.
There may be error messages posted before this
with more information about the failure.
End Error

The error message “Column does not exist.” led me to think that the stored procedure or one of the tables have been altered.

A quick look at the created date and modified date of the stored procedure and the tables confirmed that there were no schema changes.

So why the missing column error?

I executed the stored procedure manually and that revealed the issue.

There was TRY CATCH block in the procedure. It didn’t re-raise the error but returned it with a SELECT statement.

For example –

BEGIN TRY
  -- some sample sql
  SELECT
    o.OrderNumber,
    o.OrderLineNumber
  FROM
    dbo.Order as o
END TRY
BEGIN CATCH
  SELECT
     ERROR_NUMBER() AS ErrorNumber
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

So when there was any error, the error message was returned as a SELECT output.

The column names of this output were different from the original columns. This caused the SSIS package to fail.

Take away –
Use RAISERROR or THROW in TSQL to return the errors to the calling application. Using a SELECT for this purpose can confuse the calling application to consider that as a data set.