I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded.
If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint.
Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables?
In the package level I have TransactionOption = Required.
The Sequence (Data Flow is inside this sequence) TransactionOption = Supported.
I had some issue in my package that I posted here:
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve.
View Complete Post