My Project components have IsolationLevel = ReadUncommitted and TransactionOption = supported.
My query is a select on table Y verifying if not exists in table X and the result is written to table X.
SSIS opens two ids in SQL Server, a select and a bulk. The bulk is being blocked by the select.
I tried to use NOLOCK
andSET ISOLATION LEVEL READ UNCOMMITTED
but they do not work.
I was able to resolve this checking the "Check Constraint" on "OLEDB Destination" and I don't know why.
Best Answer
to check how many transactions you have open
if all you want to do is not to be affected by blocking - and you are aware of the possible side effects - dirty reads -
you can either before the select add this line:
or within your select you can use the hint WITH (NOLOCK), example:
instead of sp_who please run the following code, and post the results here:
this will show you what is actually causing the blocking. this code comes from here:
how to find the T-SQL of a sleeping session that is blocking some other process?