We have several packages that will intermittently run until they are killed.
When looking at the Activity Monitor we can see the source query for one of the data flows is in a suspended state with an ASYNC_NETWORK_IO wait. The task will remain in the state until the process is killed.
This has been happening to several different packages on several different servers. The query that becomes suspended is always a really simple query that will run in under a second if run in SSMS.
There are 2 queries we see that get suspended often. One is an inner join from one table in a database to a table in another database. It returns ~500 rows with ~10 columns in under a second usually. The second one is just Select * from a table with ~100,000 rows.
I have been reading up on what the ASYNC_NETWORK_IO means and it looks like it is typically caused by the client application not signalling to SQL that it has consumed the data.
I have also read that it could be a resource/NIC issue but that seems unlikely to me. Some of these servers are overspec'd and the package is running on the server that holds all the data it manipulates. There should be no need for any network usage.
How can I ensure that my packages will not be suspended indefinitely?
The two servers that have had this issue recently have both been SQL Server 2014 Standard Edition. Version 12.0.5605.1 and Version 12.0.4213.0 .
EDIT2: I am pretty sure the cause is back pressure caused by a whole bunch of expensive transformations. The package has around 10 Oledb commands and a few slowly changing dimensions. I think It just needs to be re-written.