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.)

Answer 2

I found the solution which meet my requirement of sending first error and ignoring rest.

Used for loop container, declared variable Count with value = 0 and in for loop container set the condition Count < 1; Count = count + 1.