Tootle80
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
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
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.
I am using SQL Server 2008 R2 (SP1) on Windows Server 2008 R2 Standard with Service Pack 1.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
var :
<checking my variable with a single equal sign (@CopySuccess = True)
But it is good that EE support made you find your way
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/