Serving Warm SSIS Errors

  • Frank A. Banin

    SSCommitted

    Points: 1558

    Comments posted to this topic are about the item Serving Warm SSIS Errors

    Frank Banin
    BI and Advanced Analytics Professional.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice article, but why don't you use the system variable @ErrorDescription, instead of fetching it from the logging table?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • johnbrown105 56149

    SSC-Addicted

    Points: 493

    ... sp_send_dbmail ... which is stored in the MSDN database ...

    Surely you meant the msdb database.

  • paulhutagalung

    Valued Member

    Points: 70

    I prefer to use logging table as I need to collect events I need from multiple components or even packages and report it at scheduled time.

    the problem for this script is that instead of using ssis components for email, we have to use senddbmail which mean setting a mail profile at database is a must.

    is there any way to use ssis mail task with body contains html formatted strings?

  • kpatrick

    SSC Enthusiast

    Points: 188

    SSIS already has a built-in component for sending email. What are the advantages of using a store proc that calls sp_send_dbmail over sending the email directly from the SSIS package? You can access the error code and error description as package variables and it seems you'd get more flexibility using SSIS?

  • ggareth

    SSCommitted

    Points: 1529

    Nice article but my company's security policy requires Database Mail to be deactivated. We use the SSIS 'Send Mail' task instead.

  • MWise

    SSCarpal Tunnel

    Points: 4074

    We can't use the SSIS Send Mail task because the SMTP connector does not support a remote SMTP server that requires a user id and password. We use sp_send_dbmail and a specific SQLAlert profile for all of our error notifications.

    MWise

  • cliffb

    SSCarpal Tunnel

    Points: 4547

    It's a good article and approach. I am confused however as to how you are getting just one email to send. Internally where I work we use a custom email component (handles formatting and some other things beyond what is available in the default task) and for every time the OnError event fires, an email is sent. What your procedure would do, if I understand correctly, is still send multiple emails, it's just that the last email would have all errors accounted for. Wouldn't a better design be to call that procedure on Post excute of the package to ensure only one error email was sent?

    If I am not getting something, please let me know. I'd love to have something we can implement here where we only get one email.

  • Frank A. Banin

    SSCommitted

    Points: 1558

    CliffB,

    You are right, the task that calls the SP should be assigned to the on post execute event, I made this change after the article was approved so I it did not take.

    Sorry for joining the party a little late I am actually on vac with limited Internet access. But let me know if you have any questions.

    Thanks

    Frank Banin
    BI and Advanced Analytics Professional.

  • sqlserver8650

    Ten Centuries

    Points: 1091

    Thanks

    Good Article

  • dgreen-1126628

    SSC-Addicted

    Points: 443

    [font="Courier New"]Here's the way I send out a single error message (error message 1) to my pager.

    1) Set up a variable at the package level as an error counter; such as "ErrorCounter" as Int32

    2) Add a SQL Task to the OnError event handler at the package level.

    3) Connect it to your SQL Server database and use direct input with the code "select ? + 1 as counter" to increment

    4) In Parameter Mapping on the task, use the "User::ErrorCounter" variable as Input as LONG for Param1 with size -1

    5) In Result Set, use "counter" as Result Set and "User::ErrorCounter" as the variable

    6) Add a Send Email task to the event handler and connect the SQL task to it with a Precedence Constraint

    7) Set the Precedence Constraint to Evaluate an "Expression" and set the expression to "@ErrorCounter==1"

    8) add the email info to send the email (in my case to my pager)

    9) Add two Expressions to your Send Mail task:

    (1)MessageSource with formula = "Error No.: " + (DT_STR, 10, 1252) @[User::ErrorCounter] + "\rError Code: " + (DT_STR, 25, 1252) @[System::ErrorCode] + "\rError Description: " + @[System::ErrorDescription]

    (2)Subject with formula = "Error No.: " + (DT_STR, 5, 1252) @[User::ErrorCounter] + " in SSIS Package: " + TRIM( @[System::PackageName] )

    I also send out all the error messages to my email. To do this, copy the Send Email Task and attach it to the SQL Task

    with a normal Precedence Constraint (green line) and the emails will flow in one at a time per error message. They

    are usually not that helpful, but sometimes they can be. I don't mind having all the emails to look at when I get paged.

    I seldom get paged, but when I do, I don't mind looking at the emails to find the issue(s). I also have not yet had

    to deal with logging errors in SSIS using my method.[/font]

  • Kevin Kunz-193240

    Valued Member

    Points: 73

    A nice article, but for people to "drive and learn" directly from the article they would need your table scripts for error logging - that or decipher the stored procedure, ensuring proper data types and lengths in order to get the process to run.

  • MWise

    SSCarpal Tunnel

    Points: 4074

    Kevin Kunz-193240 (4/12/2012)


    A nice article, but for people to "drive and learn" directly from the article they would need your table scripts for error logging - that or decipher the stored procedure, ensuring proper data types and lengths in order to get the process to run.

    The article uses the standard sysssislog table that is created when logging is enabled in a package. Although I do believe the stored procedure is limited to SQL 2008 and above. IIRC, SQL 2005 used a different table name. I was able to follow the steps in the article and send myself some nicely formatted error messages with all the necessary details.

    MWise

  • Singanan Krishnasamy

    SSC Enthusiast

    Points: 192

    good stuff. appreciate for the wonderful demonstration.. thanks a lot,

    meanwhile, I am thinking that we need to have some alternate ways as well to capture error log information (* in addtition to database table) ...the reason is that, what if we have connection issues to the errorlog table itself ?

  • DBGuy1611

    Grasshopper

    Points: 15

    Useful article. I too prefer to use the send mail task but thought provoking nonetheless. Thanks for taking the time to pull the article and samples together!!!!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply