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

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

  • 1. I would suggest checking these settings as well on the other side of the pipe if you are sending/receiving data from another computer. 2. Though this is probably not the issue as it's less likely. You should ensure that your SQL network packet size is below 16k. – Josh Simar Mar 10 '16 at 2:34

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.