April 27, 2011 at 4:52 pm
SSIS guru's,
I am running a delete statement in Execute SQL task passing a variable say @ID as input parameter.
SQL statement: delete from <tbl_name> where id = ?
Problem here is because of some locking issues our organization business requirement is running this delete statement till it get succeed at most 10 times else we have to fail the package. To achive looping functionality, I placed this execute sql task in for loop with an evaluating expression (@Errcount < 10 && @Err !=0). To check whether the task is succeed or failed I am writing assigning @@ERROR value to @Err variable is result set.
Say when the task succeed on 5th attempt the @@ERROR count is return a value 0 into result set variable @Err ( because I checked @Err value in Script task MsgBox) but the execute sql task is not changing its color from red to green. I didn't change the properties of the package or execute sql task or for loop container. I changed the property "max error count" to 0 so that task won't stop at the first attempt on failure.
Any thoughts ??
April 29, 2011 at 12:36 pm
Is the delete statement executed successfully? Does the package finish with an error?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 29, 2011 at 2:20 pm
yes it is failing with deadlock victim error.
April 29, 2011 at 2:34 pm
I would alter the query to make it more performant and find what else is causing it to deadlock when it dies.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2011 at 4:21 am
sivaprasadyellala (4/29/2011)
yes it is failing with deadlock victim error.
You can run SQL Server Profiler on the server when the package is running to figure out why there's a deadlock and figure out how to prevent this.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply