TCP Provider: The semaphore timeout period has expired in SSIS

Question

Sometimes during loading data in SSIS from SQL Server to SQL Server 2016 (using Data Flow) I get following errors:

SE [[209]] Error: 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 11.0" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: The semaphore timeout period has expired. ".

[SSIS.Pipeline] Error: SE failed the pre-execute phase and returned error code 0xC0202009.

I cannot find any pattern when I get this. Anyone had the same problem and solved it?

Solution

I had this elusive problem before and searched for it, the only notion on MSDN forum and SO question "how to resolve DTS_E_OLEDBERROR. in ssis". In a nutshell - issue is related to high load of SQL server or its runtime environment. In my case, error disappeared after adding more RAM to SQL server and configuring TCP window = 32767; however, I am not convinced it was the cure.

Comments

[Testtest11]

You meant adding RAM to which SQL Server? Source or SSIS instance?

[Ferdipux]

@Testtest11, in my case RAM was added to SQL server which provided data to SSIS.