April 27, 2011 at 4:55 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 28, 2011 at 1:46 am
sivaprasadyellala (4/27/2011)
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 ??
Can you try forcing the execution value to Success when the @@ERROR count = 0?
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
May 10, 2011 at 7:38 am
Does the table have a lot of records (millions)? If so, you may want to look into doing your delete statement in batches. I've had many problems with trying to do a delete wholesale on large tables.
May 11, 2011 at 2:35 am
Run the package using dtexec instead of using the GUI in BIDS.
Does it run successfully? Are there any errors/warnings in the log?
If the answers are yes and no, then there isn't a problem.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply