I have a SSIS 2005 package with a dataflow that does a merge join between the
source table on another server and the staging table to determine which rows
should be inserted into the staging table and which rows should be deleted
from the staging table.
However it seems like there's a kind of locking problem as the proces doing
the delete is locked by the proces doing the insert operation. The insert
operation is waiting for ASYNC_NETWORK_IO and never completes.
If I manually delete all rows from the staging table before the dataflow
runs it completes successfully in a few seconds. The OLE DB destination
doesn't do table locking. Why doesn't the insert operation complete as it in
SSMS Activity monitor doesn't wait for any locks and how can this problem be
avoided?