I have an SSIS package that runs daily in SQL agent, there are days that the job is failing due to "Communication Link Failure" but sometimes successful. I search the net for some solutions and there are recommendations to disabled TCP Chimney Offloading, RSS and NetDMA. So i disabled those features but still the error is recurring. What possibly causes the failure? Thanks
Sql-server – Communication Link Failure in SQL
sql serverssis
Best Answer
below are the options we have tried in our environment and we are successfull in reducing timeouts to 99%.
1.Change remote login time out options
2.change remote query time out options
3.Disable tcp chimney,rss options
http://blogs.technet.com/b/onthewire/archive/2014/01/21/tcp-offloading-chimney-amp-rss-what-is-it-and-should-i-disable-it.aspx
Once the above settings are in place,its time out to look on queries
1.Gather the most failed step by querying ssisdb catalog by limiting predicate to event of onerror
2.Once the above steps are gathered,concentrate on most failed steps and post the query here if you need any help in fine tuning.
3.further you also may need to check some aspects like
what is the load on the server
are my queries waiting for any resources consistently
are the failed queries using tempdb,if so what is the load on tempdb
once the above changes are done with respect to settings,we concentrated totally on Tuning queries and it yielded results