Link to home
Start Free TrialLog in
Avatar of Tootle80
Tootle80Flag for United States of America

asked on

SSIS For-Loop Container Error Handling

Hello Experts,

I am trying to handle an error in a For-Loop container and have it continue in the loop, retrying the operation up to a defined number of times before finally failing.

I have a for-loop container that, within it, has a sequence container.  The tasks in the sequence container are to copy a database, check to see that it copied successfully and, if so, move on.  If it failed, it waits a defined period of time, then should loop, trying the copy again.

The For-Loop container, Sequence container, and Copy task each has MaximumErrorCount = 0.  Each has an onerror event handler defined (with nothing in it), and the Propagate system variable set to False.


I am forcing the copy task to error by having the compatibility level set incorrectly on the database.  Unfortunately, it seems that when it gets the copy error, it terminates the loop and moves on, apparently with success.  However, the success precedence constraint on the For-Loop container is defined as a logical "and" that requires a success status, and a package level variable called CopySuccess to be set to true.  That is done in the success constraint of the integrity check, which follows a successful copy of the database.  The success constraint is apparently never getting executed, so I don’t understand how it continues as though it was successful.

 The attached file shows screen shots of the package execution, and some details concerning the containers and variables.

Can anyone help me understand what I need to do to continue the loop when the copy task fails?

Many thanks! ETL.pdf
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

please post what is  your sql server version, edition, service pack..

check these articles
SSIS Foreach Loop Container: Continue on Error
http://sqlserverselect.blogspot.com/2010/12/ssis-foreach-loop-container-continue-on.html


Continuing a Loop After an Error
http://agilebi.com/jwelch/2008/06/29/continuing-a-loop-after-an-error/
Avatar of Tootle80

ASKER

Thank you.  I had seen these two articles.  I believe I've implemented what they suggest - enabling an error handler, then setting the Propagate system variable to False and set the MaximumErrorCount to 0.  

I am using SQL Server 2008 R2 (SP1) on Windows Server 2008 R2 Standard with Service Pack 1.

Update:

I have discovered that the process is executing the For-Loop container the specified number of times (3 in this case).  I put a send mail task in the on-error event of the Copy task.  I received 3 e-mails saying the copy task failed.  The copy task is contained in the sequence container, but neither the success or failure constraint is executed after the copy fails.  I have propagate set to false and maximumerrorcount at 0.  It simply drops out of the for-loop container after 3 iterations and executes the success path.  I have a failure path set to send an e-mail, but that never gets run.
ASKER CERTIFIED SOLUTION
Avatar of Tootle80
Tootle80
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Took some work, but finally figured it out.
there were no indications in your Q about usage of
 var :

<checking my variable with a single equal sign (@CopySuccess = True)

But it is good that EE support made you find your way