3

Good day, I have an SSIS package that runs on scheduled job for a while now. all was okay, until very recently, i started receiving some failed job executions. When I investigated it, I found the following errors:

Error: 0xC0202009 at PACKAGE_NAME, Source - Query [127]: 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: "Protocol error in TDS stream".

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: A connection attempt failed because
the connected party did not properly respond after a period of time, or established
connection failed because connected host has failed to respond.".

Error: 0xC0047038 at PACKAGE_NAME, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on Source - Query 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.

Can anyone help?

  • looks like whatever server your SSIS package was connecting to is no longer responding. Try finding out where the server is and why its no longer responding. – Craig Efrein Nov 3 '14 at 12:49
  • the ping is continuous towards the source server... this is what makes of it a strange error, or not? – Hani El Mouallem Nov 3 '14 at 13:17
  • You can ping, OK, but can you connect to the MSSQL instance from the server that's running the SSIS package? – Craig Efrein Nov 3 '14 at 13:20
  • the connection drops when the query is executed:Msg 121, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) after fetching few hundreds of records, what do you advise? – Hani El Mouallem Nov 3 '14 at 14:13
1

The semaphore timeout period has expired" is a Network error

  1. Network issues between the source server and the server running SSIS?

A look in the Windows System logs of both machines might provide more information about any network issues. You will probably also find the specific windows error number, which will help in your research.

  1. Locking/blocking ?

Using the dmvs dm_exec_connections, dm_exec_sessions and dm_exec_requests you can see if another query is blocking access to a resource your SSIS package is requesting from the source server.

select es.session_id, er.blocking_session_id, * 
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
  1. Is the query too slow because of full table scans or lack of indexes?

  2. Remote Query Timeout: Is the remote query timeout set on the source server set?

    EXEC sp_configure 'show advanced options', 0 GO RECONFIGURE GO

At 0 the default value, there is no timeout.

-2

I've recently discovered the same problem. It seems to be a problem when using Native Client 10.0 (even in SQL2008 R2 SP3). Install a later Native client, eg. 11. Or use the older "Microsoft OLE DB Provider for SQL Server".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.