Random SSIS Error Code DTS_E_PRIMEOUTPUTFAILED

Question

I've seen many posts about this error, the difference is that i'm having this error some days yes, some days no and I can't establish a relation to anything. If I re-run the SSIS package inmediatly after the error it usually works ok.

I've changed the sql driver, as I saw on many posts related to this error, but it doesn't work.

So... this is the case: each time the SSIS package fails I'm having 2 error messages

Error1: -1073450952: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "XXXX" (30) 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.

Error2: -1071636471: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ".

Thanks in advance for your time and help!

Answer 1

This sort of issue is fairly common and practically impossible to track down and eliminate.

I would initiate the SSIS Package from a SQL Server Agent Job. In the Job Step definition I would set it to retry e.g. 3 times with a 5 minute delay.

Answer 2

It's possibly a concurrency issue. I had a package where I read from a source table, then read from the destination table, then did some transformations, and finally the survivors got updated in the destination table. Depending on how many steps are between, how intricate the logic is, and what network traffic is like that day, the process may reach different checkpoints on different days before locking up.

I found from the Activity Monitor that my write was waiting for the rest of the rows. What it didn't tell me was that my read was blocked by the table-lock on the write. I forced a blocking operation by inserting a client-side sort between the read and the write. Other possible solutions involve writing to a temp table; turning off the table-lock on the destination write; redoing the logic so that you don't need the destination; or splitting them up into different Data Flow Tasks and enforcing execution order with precedence arrows.

If you're doing an "upsert" (insert or update) operation, try using a pattern similar to what's described here: http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/62063/

Answer 3

I had similar problem with my package. My source is mySql database so I am using .Net providers. MySql Provider. And during the import after around 30 k records I have error in my source block:

Error1: -1073450952: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "XXXX" (30) 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.

I fixed it with changes in advanced option for that connection manager (MySql Provider):

I set value = 10 for keep alive and I increased connection timeout value.