question

nidheesh.r.pillai avatar image
nidheesh.r.pillai asked

TIP: How to automatically resubmit the query that was involved in the deadlock inside SSIS?

I was reading the [Handling Deadlocks][1] page and I came upon this quote- > Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation and take remedial action (for example, automatically resubmitting the query that was involved in the deadlock). By resubmitting the query automatically, the user does not need to know that a deadlock occurred. My problem is that I have several SSIS packages that run simultaneously and interact(Read/Write) with many of the common SQL Server physical tables between them. Many a time, the SQL processes/SSIS tasks in these packages enter into a deadlock state, and then one of the process inside one of those package is chosen as the victim with the 1205 error message, thereby failing that package. The remediation currently is to manually re-run the SSIS package/process that failed. I would like to know how would the remedial action (automatically resubmit the query that was involved in the deadlock) be implemented inside SSIS package(s)? [1]: https://technet.microsoft.com/en-us/library/ms177453(v=sql.105).aspx
sql-server-2008ssisdeadlockerror-trap
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Grant Fritchey avatar image
Grant Fritchey answered
I'd make the call into a stored procedure where you can do the proper error handling and retry logic.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.