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.
