Read source data without waiting for possible locks

hi!

I wonder if anyone can tell me how we can run select queries in an OLE DB data flow task and tell the target SQL 2000 server it should allow reads at all time. Currently when a lock is on the source table our SSIS package will sit and wait untill the lock on the source table is gone.

Thanks.

Marc

Try issueing the READUNCOMMITTED or NOLOCK switch.

http://msdn2.microsoft.com/en-US/library/ms187373.aspx

|||

Hi,

When I simulate a lock on a table with:

begin transaction
update table set column = 'test' where columnkey = 1

and I execute the package reading from this table it will wait untill I commit the transaction. If I change the select statement in the package in select ....... with (nolock) and try running it again it still keeps waiting. However, when I execute the same SQL statement in management studio I will get the results.

Can you explain this?

Thanks

|||

Use NOLOCK on your SSIS query.

Management Studio may be issuing that behind the scenes. (You're deep into Transact-SQL territory here, and there is a better forum for this discussion.)