SSIS send email on error sending multiple errors

1
votes

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.

5
ssis

5 Answers

12
votes

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.

2
votes

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.

1
votes

One method to send a single mail containing a summary of all the error messages is:

  1. Create a String variable to store the error description summary.
  2. In the OnError Event handler, use an Expression task to store the error description as below:

@[User::ErrorDescSummary] = @[User::ErrorDescSummary] + @[System::ErrorDescription]

  1. Towards the end of your package, add a Send Email task, use the Precedence Constraint editor to set Evaluation Operation to "Expression" and check for

@[User::ErrorDescSummary]!=""

We are sending Error Email with a complete error summary if the variable is not empty.

1
votes

Thanks Ajay Kumar Meda, a small change in your solution works fine:

One method to send a single mail containing a summary of all the error messages is:

  1. Create a String variable to store the error description summary.

  2. In the OnError Event handler, use an Expression task to store the error description as below:

    @[User::ErrorDescSummary] = @[User::ErrorDescSummary] + @[System::ErrorDescription]

  3. Go to Task Failed in the event handler and add the Mail Task using the 

    variable @[User::ErrorDescSummary]
0
votes

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.

This is not working I'm getting multiple mails of error there one script task which send the status mail like job id status starttime and end time total time.This mail of this task getting as many of error occur in package