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
Tootle80Asked:
Who is Participating?
 
Tootle80Connect With a Mentor Author Commented:
I finally got it to work.  In my precendence constraint, I was checking my variable with a single equal sign (@CopySuccess = True).  I put in double equal signs (@CopySuccess==True).  I also think that having multiple success/fail paths into a single task caused problems, so now every task has its own subsequent task.  I also had to fool around with setting a fail status specifically on my for-loop container outside of the sequence container if I was going to exceed my loop counter.
0
 
Eugene ZCommented:
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/
0
 
Tootle80Author Commented:
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.

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Tootle80Author Commented:
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.
0
 
Tootle80Author Commented:
Took some work, but finally figured it out.
0
 
Eugene ZCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.