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.

OLE DB DESTINATION and SQL Server Destination

Hey All:

I was totally confused.

When designing the SSIS dataflow part, firstly , i tried SQL Server Destination because my target server is a sql server.

then execute the task with failure.

Then i tried to use OLE DB DESTINATION instead of SQL Server Destination.

This Dataflow worked.

i can not figour out why.

By the way , i used the connection is OLE DB.And i choosed OLE DB source as the datasource cuz i can not find SQL server datasource.

Who can tell me some reasons for this?

Have you searched Books On-Line?

The SQL Server destination requires that you have SQL Server running on the same machine that you are executing the package on. Also, just to have SQL Server running on the machine isn't enough; it has to be your destination. The SQL Server destination is an in-memory operation, essentially.

The OLE DB Destination isn't bound to those constraints.

|||

Using the OLE-DB destination is fine, and is probably the most common choice. Obviously you will then use an OLE-DB connection, selecting the OLE-DB provider for SQL Server. That is all good, don't worry.

You have however and advanced choice with the SQL Server Destination. It has options that make it the faster than OLE-DB, and one way they do this is by using the shared memory connection method. As you might guess from the name it means that the SSIS package pipeline and the SQL Server must be on the same machine. This makes for hard work when you wish to develop against a server running on a different machine to the development tools. For this reason I generally avoid it, unless I am really concerned with insert performance, and more often than not the bottle neck is elsewhere so the SQL Server Destination is over kill anyway.

|||

Many thanks

But the new issue is that dam slow~~

60,000 rows from a static table which server loactes in Germany to the US server costs over 30 minutes.

even worse than <select .. openquery()>

why?

|||

Solved!

i used a txt flatfile as a buffer intermedia.

|||If the "buffer" is between two SSIS tasks or packages, then use a raw file. Raw files are faster to read and write than txt files, as they are the pipeline engine's buffer structures from memory straight onto disk, without any translation or interpretation. See teh Raw File Source & Destination.|||

DarrenSQLIS :

Rock~