SSIS OLE DB Error on package execution 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?
The semaphore timeout period has expired" is a Network error 


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.


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



Is the query too slow because of full table scans or lack of indexes?
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.