locked
SSIS send email on error sending multiple errors RRS feed

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

    Thursday, April 1, 2010 1:01 PM

Answers

  • I think you are right I should use variable type int instead of string. Anyway I found the solution.

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

    Thanks for help.

    • Marked as answer by Pramod Chavan Saturday, April 3, 2010 8:40 AM
    Friday, April 2, 2010 10:00 AM

All replies

  • This has to do with the way that errors "bubble up" the stack.  If you would like to send just one error message, you can limit this by including a dummy task or container, connect it to the send mail task, and finally add a script task.  Between the dummy container and the send mail task, set a precedence constraint set to if send mail == true or sent mail count == 0 or something to that effect.  In the script task, set send mail == false, or increment sent mail count.  Just something to allow for you to determine whether an email has already been sent.

    You can also use a method of capturing the full error stack and then sending this out in the on post execute, as described in John Welch's Post Handling Multiple Errors in SSIS and Handling Multiple Errors in SSIS Revisited .

    EDIT: Of course, after reading the question again, it seems that you have seen these solutions or something like them.  Without the use of a script task, you would need to find another way to set the values.  You could do this with an execute SQL task and simply write a query like Select 1 as Count and then put the value of Count into the sent mail count variable.

    Hope this helps.


    Please mark answered posts. Thanks for your time.
    • Proposed as answer by Eric Wisdahl Friday, April 2, 2010 12:35 PM
    Thursday, April 1, 2010 1:10 PM
  • Thanks Eric.

     

    I tried using SQL task and it sends only first error in the email but package execution never ends.

    I'll brief what I have done. Created variable @SentEmail with value False then created dummy task with its output joined to Send email task with precedence constraint @SentEmail == False. After that added new SQL task where wrote query Select 'True' and set the value to @SentEmail.

     

    Do you see any problem with this?

    Thursday, April 1, 2010 1:51 PM
  • What variable type are you using and what is the type coming out of the select statement?  From the query you listed it would appear that you are returning a string, when you should be returning a bit or boolean.  I would stick with an int for this just to keep things simple.


    Please mark answered posts. Thanks for your time.
    • Proposed as answer by Eric Wisdahl Friday, April 2, 2010 12:35 PM
    Thursday, April 1, 2010 2:44 PM
  • I think you are right I should use variable type int instead of string. Anyway I found the solution.

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

    Thanks for help.

    • Marked as answer by Pramod Chavan Saturday, April 3, 2010 8:40 AM
    Friday, April 2, 2010 10:00 AM
  •  not This not working
    Wednesday, May 30, 2018 9:24 AM
  • No,This is not working after doing so again I'm getting 3 to 4 mails on Error in Package
    Wednesday, May 30, 2018 9:25 AM