Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

HOW TO CONTINUE PROCESSING IN A FOR LOOP CONTAINER AFTER ERROR HAPPENDS Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 2:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 27, 2013 8:02 PM
Points: 163, 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.
Post #1374040
Posted Wednesday, October 17, 2012 2:43 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 185, Visits: 902
I don't know the answer for your question. Sorry for that.


Alex Suprun
Post #1374059
Posted Monday, October 29, 2012 12:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 29, 2012 3:32 AM
Points: 122, Visits: 208
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.
Post #1378125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse