SSIS PrimeOutput Error?

Question

We have an SSIS job that has been running for over a year with no issue. The job takes a data set from a select statement in an oracle db and transfers the result to a table on a SQL Server 2005 instance.

As of this morning, we receive the following error message:

Error: 2010-05-26 05:06:47.71 Code: 0xC02090F5 Source: [job_name] DataReader Source [793] Description: The component "DataReader Source" (793) was unable to process the data. End Error Error: 2010-05-26 05:06:47.71 Code: 0xC0047038 Source: job_name Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (793) returned error code 0xC02090F5. 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 Error: 2010-05-26 05:06:47.71 Code: 0xC0047021 Source: P... The package execution fa... The step failed.

Does anyone know what a root cause might be?

Answer 1

I had a similar issue loading data and as in your case found the error too general so I checked the option to "Include Step Output in History" which can be found on the Advanced Options of the SQL Server Job Step. This helped me tremendously since the error was a lot more helpful and helped me determine that the problem was on the source. Hope that helps you debug.

Answer 2

There may be error messages posted before this with more information about the failure.

Did you look for other more specific error messages?

Are you logging errors or steps as they run? If so did you look in the logging table? If you aren't logging in you SSIS package, I'd set that up as part of this fix, it will make it immeasurably earier to find the problem.

Things I would consider: schema changes, permissions changes, any recent software, operating systems updates to the servers involved, data mismatches (the first time perhaps that the Oracle table held data that couldn't be inserted into the SQL table - check columns that don;t directly match first, string data that might get truncated, dates stored as strings that need to convert to datetime, etc.).