Re-run SQL task on Failure

Question

Re-run SQL task on Failure

priyaponnuram

SSC Veteran

Points: 217
More actions
May 6, 2009 at 10:17 am

#133787

Hi all,
I have a database backup (as an Execute SQL Task) in SSIS. On failure, I want execution to wait for 2 minutes and then re-run the backup task again. I want this to be attempted 3 times and devoid of failure or success continue with normal execution.
Is this possible?
Any help will be greatly Appreciated.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I think so, yes. At a minimum you can have a script task that your SQL task flows to on-error when err_cnt = 3 flow to the same next task as it would on-success.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

priyaponnuram SSC Veteran Points: 217 More actions · Answer 2

Hi RBarryYoung

Thanks! But I am unable to loop the Script Task back to the Backup Task (i.e. two connectors, one onfailure and the other normal flow).

I get the following error:

"A task would indirectly depend upon itself. To create this connection, remove one of the other connectors."

Am I missing something? Or did you mean Event-Handlers? But then how would I loop it to the backup task again.

Thanks in advance once again

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Hmmm, apparently SSIS does not allow hand-made loops like that (I had never tried it before). Let me see if I can find something else...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

You know, it just occurred to me that the For Loop container should be able to handle this (duh).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Yes, definately the for loop container around your execute sql task. That's the easiest way.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Call me old fashioned but as its just a database backup why not do it as a SQLAgent job? They have configurable retry options

---------------------------------------------------------------------

priyaponnuram SSC Veteran Points: 217 More actions · Answer 7

Thank you all! Why did the For Loop not occur to me! :w00t:

I supposed I'd have to use a script task for the 2 minute wait.

Also this backup task is part of a bigger SSIS package and has to occur in a certain sequence and thats why I can't use SQL Agent.

Many thanks.