Execute SQL task is not changing to green color on success

  • 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 ??

  • 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

  • yes it is failing with deadlock victim error.

  • 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

  • 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