SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HOW TO CONTINUE PROCESSING IN A FOR LOOP CONTAINER AFTER ERROR HAPPENDS


HOW TO CONTINUE PROCESSING IN A FOR LOOP CONTAINER AFTER ERROR HAPPENDS

Author
Message
wnarretto
wnarretto
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 85
ISSUE: HOW TO CONTINUE TO PROCESS THROUGH A SET OF VALUES USING A FOR LOOP CONTAINER WHEN THE CONTAINER ERRORS.

SITUATION: I HAVE 6 FOR LOOP CONTAINERS. EACH CONTAINER HAS MULTIPLE EXECUTE SQL TASKS THAT WILL GRAB THE TOP 1 ID FROM A CONTROL TABLE AND PROCESS DATA ACCORDINGLY. ONCE THE PROCESS IS DONE FOR THAT ID, THE LAST STEP IS TO UPDATE THE CONTROL TABLE TO SET A FLAG SO THAT WE KNOW IT WAS PROCESSED.

PROPOSED HANDLING OF ERRORS: IF THE CONTAINER FAILS FOR WHAT EVER REASON WE WANT AN EVENT HANDLER TO CAPTURE THAT ERROR AND THEN SEND OUT AN EMAIL THAT NOTIFYS THE DBA OF AN ERROR. NEXT WE WANT THE PROCESS TO CONTINUE TO THE NEXT UNPORCESSED ID AS IF IT DID NOT ERROR OUT.

NOTE: I KNOW HOW TO SET UP AN EVENT HANDLER FOR THE CAPTURING OF ERRORS AND SENDING OUT EMAILS BUT I DON'T KNOW IF IT IS POSSIBLE TO JUMP BACK TO THE TOP OF THE PROCESSING STEPS FOR THE NEXT ID. BASICALLY IF ERROR THEN LOG, EMAIL AND THEN CONTINUE LOOPING THROUGH THE OTHER UNPROCESSED ID'S.

PROCESS FLOW:
1. GRAB TOP 1 UNPROCESSED ID
2. PROCESS DATA ACCORDINGLY
2A. IF ERROR HAPPENDS, END PROCESSING OF THAT ID AND THEN GOTO STEP 1 TO PROCESS UNPROCESSED ID
3. UPDATE CONTROL TABLE

P.S.
WHY IS THERE NO LOCATION FOR SSIS 2008? AND YES I JUST REALIZED THAT I AM TYPING IN UPPER CAPS. SORRY FOR THAT. :-D
Alexander Suprun
Alexander Suprun
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 1516
I don't know the answer for your question. Sorry for that.


Alex Suprun
Kranthi Babu
Kranthi Babu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 209
Yeah you can do this. Create OnError Event handler. There you can see the propert "Propogate". Set this value to True. So whatever tasks present in OnError are executed followed with loop execution for the remaining sets.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search