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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Tootle80Author 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tootle80Author Commented:
Took some work, but finally figured it out.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.