Tuesday, June 12, 2012

Least Helpful SSIS Error Message: DTS_E_PRIMEOUTPUTFAILED

Short Description:  An SSIS package throws this error on an OLEDB source component

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "My OLEDB component" (1729) returned error code 0x80004005.  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.

Unfortunately, the actual error is almost always downstream from the OLEDB datasource, but isn't always reported.  If you're very, very, lucky and the downstream component hits  the error condition before the OLEDB source fails the primeoutput, the actual error may be recorded earlier in the log/Execution Results.   It seems that the error basically means that somewhere downstream there is a component that either doesn't like, doesn't fit, doesn't understand, or is missing a condition to deal with the data returned by the OLEDB datasource.  Turning up logging (SSIS menu, select logging, check the failing dataflow and add a provider (make sure to check the box next to it to enable it), then decide how much detail you want on the details page)  may or may not result in the error being logged.  If it doesn't show up with verbose logging, I'm not sure of a better way than triple checking, and trial and error removing components to locate the problem.

Just in case it's helpful, here are some times I've run into this condition while writing packages:

  • Unhandled null in a downstream conditional split
  • Trying to retrieve an Oracle old-school, deprecated "Long" datatype with the Microsoft OLE DB data source -- spoiler alert:  it doesn't work...it looks like it's working, but treats it as a text stream and as far as I can tell it then fails once it gets to one that doesn't look like enough like a text stream.   I don't know how to fix this other than phasing out datatypes deprecated last millennium.
  • Constraint violation (in this case, not null) on a downstream component.

Obviously, these conditions should cause the package to fail, but a more clear error message would be a real time saver.

No comments:

Post a Comment