OLE Db Destination task in ASYNC_NETWORK_IO wait state

I have established an SSIS dataflow that should move 20,000 records from a source table to a target table. Only new records should be added to the target table – existing records should be ignored. The problem I am reporting occurs when the target table is initially empty (there are no existing records, so everything should come over). I am using the Slowly Changing Dimension task to limit inserts to new records. The tasks in my data flow are:

OLE DB Source è Slowly Changing Dimension è Derived Column è OLE DB Destination

The problem is that the data flow locks up before completing. This occurs if the Data Access Mode of the OLE DB Destination task is “Table or view – fast load”. If I look at the Activity Monitor in SQL Server Management Studio once it has locked up, I can see two processes in the suspended state. Based on the queries, I can identify one of the processes as performing the lookup for the Slowly Changing Dimension task. The other represents the BULK INSERT associated with the OLE DB Destination task.

The Slowly Changing Dimension task is locked waiting for the OLE DB Destination task as determined by looking at the “Blocked By” column in the Activity Monitor. Its wait state is LCK_M_S. The OLE DB Destination task has a wait type of ASYNC_NETWORK_IO.

QUESTIONS: Why is the BULK INSERT in the OLE DB Destination task waiting? What does ASYNC_NETWORK_IO wait state indicate? How do I prevent this from happening?

I am running the SSIS package in SQL Business Intelligence Studio on a workstation against a SQL Server 2005 server. The same situation is seen when I run the package directly on the SQL Server 2005 systems in SQL Business Intelligence Studio rather than on my workstation.

Turn off the table lock option for the OLE-DB Destination. Leaving this on means that the second buffer of data is blocked, specifically the lookup, as when the first passed to the destination the lock was acquired, and now prevents any more lookups happening.

You could also investigate adding a NOLOCK hit to the lookup SQL statement.

|||I had already tried turning off the table lock option, though I failed to mention it. Adding the NOLOCK option onto the query within the Slowly Changing Dimension task did indeed allow the data flow to complete.

Thank you.