Saturday, 10 February 2018

SSIS Snack: The semaphore timeout period has expired

Case
My SSIS package that runs in Azure Data Factory V2 (and gets data from an on-premises source) suddenly stops working and throws an communication error. After this error it won't run again. What is happening?

TCP Provider: The semaphore timeout period has expired









DFT - DIM_xxxxx: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.".



Solution
The error seems to be caused by a network communication hiccup with our on-premises source which is connected with a VNET in Azure. Although I do not know the actual cause of the error. There is a solution: restart your Integration Runtime.

1) ADF dashboard
Go to your Azure Data Factory (ADF) that hosts your Integration Runtime and click on Author & Monitor within the Quick links section. This will open the ADF dashboard.
Author & Monitor




















2) Stop & Start IR
Click on Author (pencil), on Connections and then on Integration Runtimes (IR). Then Stop and start your IR. This could take up to 30 minutes! After that rerun your package and it should be working again.
Stop & Start IR

















Please let me know in the comments whether it worked for you and if you found the actual cause or the error.

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...