January 2, 2015 at 9:49 am
Hello,
I have a package with an event handler on the package only. All of the parent containers have both FailPackageOnFailure and FailParentOnFailure set to False. Any individual tasks withing the containers have the FailPackageOnFailure set to TRUE and FailParentOnFailure set to False. MaximumErrorCount on all tasks is set to 1. My OnError event handler sends an email to a small distribution list using an SMTP connection. The package is set to run via a SQL server job nightly.
One night the job failed and sent over 500,000 emails. I have since disabled the job. I would like some advice as to what to look for to determine why so many emails were sent. There is no looping in the package.
Looking forward to hearing your suggestions!
Thanks.:-D
January 5, 2015 at 6:42 am
So it happened again. It was on a task that calls a stored procedure, passing in one variable. Like I said the MaximumErrorCount is 1 and FailPackageOnFailure is set to true. Apparently it is catching the error, running the error event code, but not treating the error as a "Failure". The stored procedure has no error handling, so I will add error handling to that as well.
January 5, 2015 at 7:10 am
Is that stored procedure executed multiple times, say in an update task?
Regards
Lempster
January 5, 2015 at 7:13 am
Errors propagate in SSIS.
For example, if you have a task in a container in a package and the task fails, you will get 3 error events at the package level.
One for the task, one for the container and one for the package.
More information:
SSIS Logging and Event Propagation
However, 500,000 emails is quite a lot, even with propagation 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 7:14 am
At second thought, it might be easier to not use event handlers but rather use a Send Email Task directly in the control flow.
Just connect it with the previous task using a red arrow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 8:30 am
Thank you for your reply. I will explore that option!
January 5, 2015 at 8:33 am
No, the stored procedure is executed once, however it contains a cursor (I didn't write it) and it does not contain error handling. So every time it ran it was trying to insert a NULL into a NON-NULL field. I have since added error handling to the stored procedure so that it just catches the error the first time it occurs and quits the procedure.
The thing that baffles me most is that the task that calls the stored procedure has MaximumErrorCount = 1 and FailPackageOnFailure set to TRUE. I thought once the error was encountered once, the event would be raised once, execute once, and the package would stop. Apparently it doesn't work that way.
January 5, 2015 at 11:13 am
Well, the maximum error count is on the package, not on the stored procedure. It doesn't know the stored proc has hit an error until the proc returns. Sounds like it returns with 500,000 errors and your error handler sends an email for each one of them.
January 5, 2015 at 12:07 pm
mz1derful (1/5/2015)
The thing that baffles me most is that the task that calls the stored procedure has MaximumErrorCount = 1 and FailPackageOnFailure set to TRUE. I thought once the error was encountered once, the event would be raised once, execute once, and the package would stop. Apparently it doesn't work that way.
I think you have to look at it like this:
MaximumErrorCount is the number of errors that a task is allowed to make. For instance if set to 3, the task will only fail if more than 3 errors are encountered.
I don't think it is a limit for the possible amount of errors.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 12:54 pm
That makes sense to me too. I think the post right before yours describes what actually happened. Since there was a cursor in the stored procedure that had an insert statement within it that produced an error and there was not error handling in the stored procedure, the event was raised as many times as the stored procedure errors.
I have since coded around this and hopefully trapped all possible scenarios!
I appreciate your input:)
January 5, 2015 at 1:00 pm
mz1derful (1/5/2015)
That makes sense to me too. I think the post right before yours describes what actually happened. Since there was a cursor in the stored procedure that had an insert statement within it that produced an error and there was not error handling in the stored procedure, the event was raised as many times as the stored procedure errors.I have since coded around this and hopefully trapped all possible scenarios!
I appreciate your input:)
The real solution is not to use cursors 😀
(although they can not always be avoided)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 5, 2015 at 1:01 pm
Like I said before, I didn't write that stored procedure! Whomever had this job before me was cursor happy:w00t:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply