“Communication Link Failure” is often due to outdated drivers or poor network configuration settings.
Please note OLEDB driver it is not maintained anymore and it is not recommended to use this driver for new development. If however, your business require the use of OLEDB, you should download and install the recent OLEDB provider and make sure you change the provider name in the connection string. Please read more about this here.
It is highly recommended to use ADO.NET whenever is possible as it provides more reliability.
In this scenario, SSIS packages were used to load data from SQL on-premises to Azure SQL DB. After a while, the bulk insert failed with below errors:
Error: 2020-08-25 10:44:08.16
Source: Extract Location Hierarchy Data to Datamart OLE DB Command 
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
Error: 2020-08-25 10:44:08.23
Source: Extract Location Hierarchy Data to Datamart SSIS.Pipeline
Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (156) failed with error code 0xC0202009 while processing input "OLE DB Command Input" (161). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Note that the same error message might appear also when reconfiguration happen on the SQL DB and SSIS is not aware of retry logic.
There are several options to be considered in order to address the above error on client side.
Unless you have a specific requirement for a legacy application, disable all the TLS_DHE ciphers, as they are outdated and not recommended to be used.
Option #1 - Follow the steps described in the documentation below:
Option #2 - Alternatively, you can download IIS Crypto Tool from here and configure the best practices on your client and server.
Steps to configure best practices:
Step #1 Ensure NIC Drivers are up to date
Step #2 Disable TCP chimney at NIC Level
TCP Chimney Offload is a feature that provides capability to offload TCP/IP packet processing from the processor to the network adapters and some other balancing options. This has been known to cause issues on the SQL Server side causing network errors. Further information on this can be found in the following blog post as well.
Make sure that under Advanced Settings of NIC below options are DISABLED:
Steps to disable TCP chimney features at NIC level:
The other properties to look for and disable are:
Step #3 Configure TCP Chimney Offload, RSS and NetDMA in the operating system.
Open an elevated cmd and type below commands.
To determine the current status of TCP Chimney Offload and RSS:
netsh int tcp show global
To disable TCP Chimney Offload:
netsh int tcp set global chimney=disabled
To disable RSS:
netsh int tcp set global rss=disabled
To disable NetDMA:
netsh int tcp set global netdma=disabled
Restart the machine.
Another recommendation to alleviate the connection issue is to actually tune your SSIS package to perform more efficiently. There’s lots of articles around this aspect, but the top settings are DefaultBufferMaxRows, DefaultBufferSize, and Rows per batch settings. However, this needs to be performed over many trials, while capturing logging data and adjusted accordingly. We usually see General Network Errors associated with poor performing packages, that degrade over time because of the data/transactions involved.
This blog gives a quick rundown about tuning your SSIS package for a better performance.
Here’s our documentation on top 10 SSIS Best Practices which will also go over different settings you can test.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.