question

SubbiahSujitha-9423 avatar image
0 Votes"
SubbiahSujitha-9423 asked SubbiahSujitha-9423 edited

Event handler on SSIS

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?

sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you provide the script (or relevant portions) and point out where the failure that causes the deadlock is occurring

0 Votes 0 ·
Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @SubbiahSujitha-9423,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SubbiahSujitha-9423 avatar image
0 Votes"
SubbiahSujitha-9423 answered

thanks for the links

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SubbiahSujitha-9423 avatar image
0 Votes"
SubbiahSujitha-9423 answered SubbiahSujitha-9423 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.