Event handler on SSIS

Question

Event handler is set for the entire dtsx package on error. There is a script task within which if an exception occurs, before the catch in the script could be executed and the variables could be unlocked, the control goes to event handler where the same variable is referenced. This behavior is leading to deadlock on that variable. Is there anyway to catch the exception and then trigger event handler only for this task?

Answer 1

Hi @Subbiah, Sujitha ,

Check this to see if it could be helpful.

http://sqlis.com/sqlis/post/A-deadlock-was-detected-while-trying-to-lock-variables-in-SSIS.aspx

https://social.msdn.microsoft.com/Forums/en-US/5dc2d1ae-e5a6-49d1-98dc-2548e76a6ca1/variables-deadlocked-in-onerror-handler?forum=sqlintegrationservices

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 2

thanks for the links

Answer 3

Hi Zoe,

Thanks for the links but I see some issues with the solution proposed in them.

The solution provided in this site is resolving the deadlock but unable to get the data (its coming as null) set within the script when used in the event handler - http://sqlis.com/sqlis/post/A-deadlock-was-detected-while-trying-to-lock-variables-in-SSIS.aspx

The solution provided in this site - ( https://social.msdn.microsoft.com/Forums/en-US/5dc2d1ae-e5a6-49d1-98dc-2548e76a6ca1/variables-deadlocked-in-onerror-handler?forum=sqlintegrationservices ) is causing the job to run endlessly as it keeps waiting to get the locked variable value

Thanks,
Sujitha