SSIS send email on error sending multiple errors

Question

I have simple SSIS package in which On Error event handler I have created Send email task to send error details. But I get multiple emails for one error. I found the ways to club all emails but they are in vb script and my SQL server is 64 bit which in turn requires hotfix installation for binary compilation. So I need any alternative option for that. It's fine with me even though I can send first error email and ignore other. Any suggestion will be appreciated.

Answer 1

The reason you are getting multiple errors has to do with event bubbling. If you have an event handler at the parent package level, and it has, for instance a data flow task, and in the data flow, there is a OLEDB Command component that fails, then that object will throw and error that bubbles up to the event handler. Then because the component failed, the Data Flow task reports an error and it bubbles up to the event handler. Then because the Data Flow Task failed, the package fails and that is sent to the event handler. I had some code where I dealt with this in the past, but I cannot get to that machine right now. I will update this question with the full answer later today, but it had to do with checking the System::SourceID variable against the System::PackageGUID variable and only running the mail portion of the error handler when they were equal. System::SourceID is the variable detailing which object is throwing the error, so this would limit the reporting of errors to those that bubble up to the package level (i.e. those that will fail the entire task.)

@Rajiv Varma

In my event handler (which is in the package level) I have a script task (which has no code behind it (basically a no-op)) and a send mail task. Between the script task and the send mail I us the constraint as a conditional (I set the evaluation operation: to Expression and the expression to the value

@[System::SourceID]== @[System::PackageID]

which tests to see if the source of the error is the main package object then I use the send mail task to send my error message.

Why this works: If I have a hierarchy of objects

Main package - Loop Task - Data Flow Task

If the Data Flow Task fails, an event is thrown. After the event throws, it bubbles up to the next level and the Loop Task throws the same error. After this event throw it bubbles up to the main package layer and throws the same error again. This is why @Pramodtech had the problem in the first place, each level was triggering the same error message again. What I am effectively doing here is ignoring any error that is not at the main package level. So I don't send any message until the message bubbles up to the top level. Hope this helps.