none
Show error message in SSIS email RRS feed

  • Question

  •  

    I have an SSIS package set up to run various SQL scripts and to notify me if any of the SQL scripts fail.  This is working fine, but when I get the email it only says that the script has failed.  Is there any way to actually show the SQL result with the error in it?  This would save me from having to go and manually run the script again to see the error message.

     

    Thanks

    Kate Smile

    Friday, December 14, 2007 8:54 AM

Answers

All replies

  • You can play with Events Handlers and use a send mail task for which you set the MessageSource property through an Expression like @[System::ErrorDescription].

     

    Friday, December 14, 2007 9:16 AM
    Moderator
  • Have you looked at using the On Error event handler? Using a handler at the package level means it will get called for any error. There are some system variables scoped to the handler such as error code and error description.

     

    I actually thing sending email from a package like this is not a good idea. The first reason is because you may have multiple errors, and having multiple emails is harder to understand than one final execution report as you get with logging. I also thing relying on something in the potentially broken SSIS package to do the alerting is risky, as who tells you when the mail task is broken? I think logging from within SSIS is good, but alerting should be done by an external monitor, or the execution host engine. The simplest example of that is use SQL Agent to execute your packages, and let it notify you of errors. Then use the SQL Agent step log and SSIS logs, as well as any custom logging you may want. Combining those sources will give you a clearer picture. You will/should already have monitoring of SQL Agent and positive checks of its alerting mechanisms, since they will be key to your SQL infrastructure and monitoring of mission critical jobs like backups.

    • Proposed as answer by S. Elben Tuesday, April 2, 2013 11:44 AM
    • Unproposed as answer by S. Elben Tuesday, April 2, 2013 11:44 AM
    Friday, December 14, 2007 10:10 AM
    Moderator
  •  

    I mostly agree with Darren, although I also send the first email through SSIS onError EventHandler.  This will give me an idea of where to start looking. Note, however, that we also have the logs running through SSIS and the SQL Server Agent, as well as trying to have a pretty strong audit trail, so that we can get a better understanding of where things went wrong.

     

    Friday, December 14, 2007 1:11 PM
    Moderator
  • Thanks for all your answers - to be honest I only use SSIS in a really basic fashion - I am a SQL coder and just wanted the error messages as they would be shown in SQL Server Management Studio to show up in my emails!  This is all getting a bit confusing and complicated for me!

     

    Thanks anyway.

    Kate Smile

    Friday, December 14, 2007 2:56 PM
  • I wrote a blog post a while back about collecting all the error messages into a variable so that they can be emailed as a unit.

     

    http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

     

     

    Saturday, December 15, 2007 3:48 AM
    Moderator
  • This is a great blog post, many thanks for this. You should mark it as Answer.

    I will certainly use this technique to streamline the error reporting.

    My wish is to keep only the relevant error message, not all the redundant messages.

     

    Right now, I simply log errors, fails and cancel to a table and I use a report to read it.

    The email is merely sent by the job and if I got the alert page, I will go look at my custom job log report,  ssis log report , report subscriptions errors reports and cube processing report for further understanding of the issue. 

    I also have a report that will show me what are the ssis packages versions in dev and prod and what was last done to the package. DBA's sometimes do not deploy the correct version, causing bugs if the rest of the deployment was done.

     

    There is no out of the box nice comprehensive solution to trouble shoot all these sub-systems, just a lot of custom work to get the full picture.

     

    May be that this is left open to third party vendors?

    They seem to focus only on the core database. As far as I know, No one offers something reaching out to jobs, packages, reports and cubes.

     

    Saturday, December 15, 2007 4:31 AM
    Moderator
  • Thanks for the blog post - it is much appreciated.

     

    However, when I attempt to email the errors it still sends multiple email messages from the "PostExecute" event handlers. This is using the "Send Mail" SSIS task, as I'm not sure how to script sending mail using the "Script Task" SSIS task.

     

    Would you be so kind as to demonstrate how one would then distribute the multiple errors once, as it still ssems to be sending multiple messages - one for each error as well as one with all error messages included?

     

    Friday, January 11, 2008 2:47 AM
  • I'd probably handle this by moving the Script task and the Send Mail task to the control flow, with a Completion constraint.

     

    Another approach would be to change the Propagate variable in the event handlers to prevent the PostExecute event from firing until the package finishes.

    Friday, January 11, 2008 4:03 AM
    Moderator
  • Thanks for the reply jwelch.

     

    Whilst the control flow would probably fix the issue, I'm looking to setup some basic error handling in a template.

     

    Therefore, I think I'll need to go with your alternative solution - using the propogate variable in the event handlers. Would you please direct me as to how you can prevent the PostExecute event from firing until the package finishes?

     

    Friday, January 11, 2008 4:17 AM
  • You have to set the Propagate variable to FALSE in the OnPostExecute event handlers for each task in the package. If I was doing this for a template, I would not take this approach, because you are depending on anyone adding a new task to the package to remember that they need to change the variable. Instead, add a sequence container to the package, and set the Propagate variable to false on the OnPostExecute event for the sequence container. Any new tasks added to the package should be added inside the sequence container.

     

    Friday, January 11, 2008 4:36 AM
    Moderator
  • If the event handlers are only on the package and not the tasks then wouldn't this still be applicable for a template?

     

    In any case, I changed the propagate value to false on the event handlers - but I am still receiving multiple emails, so I'm not sure this works as intended or I haven't set something correctly.

     

    In my test package I have one "Execute SQL Task" that I've deliberately put invalid syntax in (that parses correctly) in order to raise an error. OnError and OnPostExecute get called after the task, but both have (2) after each line in the execution results (e.g. "Start (2)" or "Validation has started (2)"). When the emails fire, the first email message contains the query exectuion error twice and the second email message contains the query execution error once. Clearly, the first message is the result of the "emailText" variable (don't know why the error appeared twice, but because the script concatenates I'm guessing this is the one I want), but I'm not sure why the second message is coming through. Any ideas?

     

     

    Friday, January 11, 2008 5:41 AM
  • Make sure that, in the event handlers, you are selecting the Execute SQL Task's OnPostExecute event and not the one on the Package. That's the one that needs to have the Propagate variable set to False.

     

    This is working successfully for me - if it doesn't fix the problem, let me know and I'll post a new example.

     

    Saturday, January 12, 2008 12:15 AM
    Moderator
  • Thanks for the reply.

     

    I changed the 'OnPostExecute' event handler to the 'Execute SQL Task' and now it sends one email only.

     

    Unfortunately, this doesn't resolve my problem though - as I need to incorporate this into a template and can't rely on people to add the event handler to every task they create.

     

    To overcome this I tried applying the same logic to a sequence container (i.e. 'OnPostExecute' event handler on the sequence container and not on the package) as per the suggestion earlier, but now I'm getting 2 email messages again.

     

    If you would be so kind as to supply an example of how to overcome this issue it would be greatly appreciated.

     

    • Proposed as answer by dgreenx Tuesday, March 2, 2010 11:24 PM
    Sunday, January 13, 2008 10:52 PM
  • I posted some updated samples here: http://agilebi.com/cs/blogs/jwelch/archive/2008/01/15/handling-multiple-errors-in-ssis-revisited.aspx

     

    They show both setting the Propagate variable and using a Sequence container. Hopefully, it will help resolve your issue.

    Tuesday, January 15, 2008 5:13 AM
    Moderator
  • Thanks for the samples jwelch.

     

    I'm now able to get just the one email firing.

     

    The thing that was confusing me was that there was an event handler 'OnPostExecute' on the sequence container, with nothing in it. But now I see this is where the propogate value must be set to false, and *ta dah* all is well.

     

    Thanks again for your assistance - it has proved invaluable for a newcomer such as myself!

     

    Tuesday, January 15, 2008 10:45 PM
  • No problem. I'm glad I could help.

     

    Tuesday, January 15, 2008 10:48 PM
    Moderator
  • Another option is to check the SourceName variable in the onCompletion event.

     

    i.e. IF @SourceName <> "Package"

     

    Tuesday, April 15, 2008 4:34 PM
  •  

    so I hate to be "that guy" that opens the thread from 4 months ago. But alas email error debugging has become and issue for me as well. More specifically is trapping invalid email addresses in the distribution list for the email. The standard System:Err msg is simply "Unable to send to a recipient." however I am unable to track down the culprit, without sending ghastly test emails to the intended recipients. So I am working on a way to try to get a beefier error message to populate.

     

    fyi I also use a sendmail task in a global On_err handler. I have yet to find a need to further compartmentalize the the error routines, until perhaps now Wink

     

     

    Monday, April 21, 2008 5:36 PM
  • i tried this solution however it seems that when i get no errors i still get emails that are blank sent to me. In addition when I get an error the function sends a seperate email for every error that is generated and not just one?
    Tuesday, August 5, 2008 3:14 PM
  • Make sure you read the second post I did about that: http://agilebi.com/cs/blogs/jwelch/archive/2008/01/15/handling-multiple-errors-in-ssis-revisited.aspx

     

    It sounds like you put the email task in the OnError event - you need to put the email task in the OnPostExecute event. To prevent an empty email from being sent, add a precedence constraint that checks to see if the error message variable is empty (double-click on the precedence constraint, choose Contraint and Expression, and put @User::YourErrorMessageVar!="" in the expression).

     

    Tuesday, August 5, 2008 3:22 PM
    Moderator
  • For what ever reason im am still getting 7 emails in the my project using your logic? For what ever i can not get the expression to clear i get an error message

    TITLE: Microsoft Visual Studio
    ------------------------------

    Error at Constraint: The variable "User" was not found in the Variables collection. The variable might not exist in the correct scope.

    Error at Constraint: Attempt to parse the expression "@User::emailText!=""" failed and returned error code 0xC00470A6. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.



    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Tuesday, August 5, 2008 4:04 PM
  • Try

    Code Snippet

    @emailText!=""

     

     

     

    Tuesday, August 5, 2008 4:33 PM
    Moderator
  • Ok so i was able to figure out what i was doing wrong. It was that i was using a sql task to send the mail, due to not knowing the internal smtp connection. So now that I am using a send mail task i am able to get the precendence to work. However i am still getting 2 mails with the propagate set to false?
    Tuesday, August 5, 2008 5:33 PM
  • And you've got the code in the OnPostExecute event handler? Can you provide a little more detail on how your package is set up?

     

    Tuesday, August 5, 2008 5:47 PM
    Moderator
  • J,

    I was able to figure it out thanks to your second link. I misread your instructions. Thank you so much for all of your help.
    Tuesday, August 5, 2008 7:16 PM
  • You will have to capture OnError Event in Package Level EventHandler or Task Level EventHandler


    try BI xPress for better auditing and notification


    SSIS-Tutorials-FAQs | Convert DTS to SSIS | Document Entire BI | SSIS Tasks | BIDS Plugin

    Tuesday, November 17, 2009 11:35 PM
  • One way to set up a template for passing the errors via email and limit the number of errors is to set up a package level variable called ErrorCounter, use it as an input parm and output in an Execute SQL task where the SQL is "Select ? + 1 as counter".  Then check @ErrorCounter == 1 in the precedence constraint line to the email.  I create two Send email tasks in my error handler tab and drag a line from the Execute SQL task to one for my pager email with a limit set for the first error only. I send all the errors to my other Send email task for my normal email.  In the expressions to set the Subject and MessageSource so they include the package name, the error counter (my package level variable), the System Error number (cast as text) and the full system error description.

    The nice thing about having the email go out on the OnError event Handler is that the process may not fail to the point that the SQL Agent in SQL Server will send out a message you may have also set up.

    I jotted down my solution in a Word Doc for safe keeping.  If anyone wants it just email me at dgreen@djgusa.com.
    Tuesday, March 2, 2010 11:35 PM
  • Very useful blog by Jwelsh. But I see that when the package succeeds, The send mail task sends a blank email. Can somebody suggest what I should be doing to have the email say that the package succeeded?
    Sunday, April 11, 2010 2:32 AM