October 17, 2012 at 2:07 pm
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. 😀
October 17, 2012 at 2:43 pm
I don't know the answer for your question. Sorry for that.
October 29, 2012 at 12:59 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply