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?

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

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.

edited May 23 '17 at 12:33

Community

1
1

answered Sep 29 '16 at 06:04

Ferdipux

5,116
1
19
33

You meant adding RAM to which SQL Server? Source or SSIS instance? – Testtest11 Sep 29 '16 at 08:40
1

@Testtest11, in my case RAM was added to SQL server which provided data to SSIS. – Ferdipux Sep 29 '16 at 10:32

TCP Provider: The semaphore timeout period has expired in SSIS

1 Answers1