Blocking a Transaction Using SSIS
I have an SSIS package that starts a transaction and then puts existing records in the destination table as inactive. Then I have a data flow task that inserts new records before executing or rolling back the transaction.
The problem I am facing is that the DF task is hanging due to a dead end. The package has been working fine for a month, but for some reason it just started blocking problems.
I've tried to set different transaction levels but no luck so far. From what I can tell here is the blocking condition:
source to share
Can you show me the execution plan? Are there any indexes scanned? if there is, you can work it out pretty quickly.
To fix the problem, we need to create a non-clustered index on the column listed in the Predicate section. The effect of this will be a nonclustered index lookup (which is a direct path to the data rather than a scan), and it will bypass the deadlock problem.
source to share