SSIS Error Handling with Email Alerts and Details.

Presented with the need for a consistent method of alerting users of Errors or Warning Conditions in SQL Server Integration Services; I went about the creation of a standard methodology to use in all packages across the enterprise.

The requirements were:

  • Alert Specific Users of Errors within a Package
  • Alerts are to provide sufficient detail for rapid troubleshooting
  • Alert Process is to be consistent across all servers

I therefore set about using the SSIS Script task, so that I could increment my counters and customize my error messages as each task in the package completed.
I implemented 2 counters, ones for warnings and one for errors as well as an error message for tracking each step of my package.
The User Defined Variables for error handling are

  • alertsubscribers – string
  • errorcount – integer
  • errormessage – string
  • mailhost – string
  • warningcount – integer

Read Only Variables (All System Provided)


System::LocaleID
,System::TaskID
,System::TaskName
,System::TaskTransactionOption
,System::MachineName
,System::PackageID
,System::PackageName
,System::ProductVersion
,System::VersionBuild
,System::VersionComments
,System::VersionGUID
,System::VersionMajor
,System::VersionMinor

Read Write Variables (User Variables at Package)


User::errorcount
,User::errormessage
,User::warningcount
,User::alertsubscribers
,User::mailhost

Script Task at Begining of Package to Initialize Variables


String errormessage = "";
errormessage = errormessage + "MachineName: " + Dts.Variables["MachineName"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "PackageID: " + Dts.Variables["PackageID"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "PackageName: " + Dts.Variables["PackageName"].Value.ToString() + System.Environment.NewLine;
errormessage = errormessage + "ProductVersion: " + Dts.Variables["ProductVersion"].Value.ToString() + System.Environment.NewLine;
Dts.Variables["errormessage"].Value = errormessage;
Dts.Variables["errorcount"].Value = 0;
Dts.Variables["warningcount"].Value = 0;

For each Error In a Script Task, if there is an Exception I simply increment the errorcount or the warning count via a line of code such as:

String errormessage = "";
int errorcount = 0;
int warningcount = 0;
errorcount = errorcount + 1;
errormessage = errormessage + "My Custom Error or Warning Message " + System.Environment.NewLine;

Dts.Variables["errormessage"].Value = Dts.Variables["errormessage"].Value.toString() + errormessage;
Dts.Variables["errorcount"].Value = (int)Dts.Variables["errorcount"].Value + errorcount;
Dts.Variables["warningcount"].Value = (int)Dts.Variables["warningcount"].Value + warningcount;

If I am running a Component, and want to track the error. I use an On Failure Script Task.

Subsequently at the end of the package there are two options.
1. Log the results to table (My preferred method)
2. Send an Email to the requisite parties. (the method I describe below)


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using System.Net.Mail;

int x = 0
x = (int)Dts.Variables["errorcount"].Value + (int)Dts.Variables["warningcount"].Value ;

// send alert only if errorcount + warning count > 0
if (x >0) {

string mailhost ="mailservername";
string alertsubscribers =Dts.Variables["alertsubscribers"].Value.ToString() ;
string subject = "" ;
string body = Dts.Variables["errormessage"].Value;

subject = Dts.Variables["PackageName"].Value.ToString() + " " + Dts.Variables["errorcount"].Value.ToString() + " Errors Encountered, " + Dts.Variables["warningcount"].Value.ToString() + " Warnings Encountered at "+ System.Datetime.Now.toString("yyyy-MM-dd HH:mm:ss")

//create the mail message
MailMessage mail = new MailMessage();

//set the addresses
mail.From = new MailAddress("genericEmailAddress@domain.com","SSIS Alert System");
mail.To.Add(alertsubscribers);

//set the content
mail.Subject = subject;
mail.Body = body;
mail.IsBodyHtml = false;
//send the message
SmtpClient smtp = new SmtpClient();
smtp.Host = Dts.Variables["mailhost"].Value.ToString() ;
smtp.Port = 25;
smtp.UseDefaultCredentials = true;
smtp.Send(mail);
}

Advertisements

2 thoughts on “SSIS Error Handling with Email Alerts and Details.

  1. Hi John

    Thanks for a brilliant article.

    I am such a novice it took me a while to realise it was in C# not VB!

    Anyway I eventually have it all working except the error trap.

    First script to declare and clear down variables:

    /*
    Microsoft SQL Server Integration Services Script Task
    Write scripts using Microsoft Visual C# 2008.
    The ScriptMain is the entry point class of the script.
    */

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Collections.Generic;
    using System.Text;
    using System.Net.Mail;

    namespace ST_e35dd1630c3249ccba313708ac502cf6.csproj
    {
    [System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

    #region VSTA generated code
    enum ScriptResults
    {
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

    /*
    The execution engine calls this method when the task executes.
    To access the object model, use the Dts property. Connections, variables, events,
    and logging features are available as members of the Dts property as shown in the following examples.

    To reference a variable, call Dts.Variables[“MyCaseSensitiveVariableName”].Value;
    To post a log entry, call Dts.Log(“This is my log text”, 999, null);
    To fire an event, call Dts.Events.FireInformation(99, “test”, “hit the help message”, “”, 0, true);

    To use the connections collection use something like the following:
    ConnectionManager cm = Dts.Connections.Add(“OLEDB”);
    cm.ConnectionString = “Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;”;

    Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    To open Help, press F1.
    */

    public void Main()
    {
    String ErrorMessage = “”;
    ErrorMessage = ErrorMessage + “MachineName: ” + Dts.Variables[“MachineName”].Value.ToString() + System.Environment.NewLine;
    ErrorMessage = ErrorMessage + “PackageID: ” + Dts.Variables[“PackageID”].Value.ToString() + System.Environment.NewLine;
    ErrorMessage = ErrorMessage + “PackageName: ” + Dts.Variables[“PackageName”].Value.ToString() + System.Environment.NewLine;
    ErrorMessage = ErrorMessage + “ProductVersion: ” + Dts.Variables[“ProductVersion”].Value.ToString() + System.Environment.NewLine;
    Dts.Variables[“ErrorMessage”].Value = ErrorMessage;
    Dts.Variables[“ErrorCount”].Value = 0;
    Dts.Variables[“WarningCount”].Value = 0;
    Dts.TaskResult = (int)ScriptResults.Success;
    }
    }
    }

    — Second script – on the Event Handler tab, OnError – to trap errors (it’s the ErrorMessage which is coming out blank)!

    /*
    Microsoft SQL Server Integration Services Script Task
    Write scripts using Microsoft Visual C# 2008.
    The ScriptMain is the entry point class of the script.
    */

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Collections.Generic;
    using System.Text;
    using System.Net.Mail;

    namespace ST_9f54ec76ddac4dc28ed885169af6a12b.csproj
    {
    [System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

    #region VSTA generated code
    enum ScriptResults
    {
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

    /*
    The execution engine calls this method when the task executes.
    To access the object model, use the Dts property. Connections, variables, events,
    and logging features are available as members of the Dts property as shown in the following examples.

    To reference a variable, call Dts.Variables[“MyCaseSensitiveVariableName”].Value;
    To post a log entry, call Dts.Log(“This is my log text”, 999, null);
    To fire an event, call Dts.Events.FireInformation(99, “test”, “hit the help message”, “”, 0, true);

    To use the connections collection use something like the following:
    ConnectionManager cm = Dts.Connections.Add(“OLEDB”);
    cm.ConnectionString = “Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;”;

    Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    To open Help, press F1.
    */

    public void Main()
    {
    String ErrorMessage = “”;
    int ErrorCount = 0;
    int WarningCount = 0;
    ErrorCount = ErrorCount + 1;
    ErrorMessage = ErrorMessage + ” – My Custom Error or Warning Message ” + System.Environment.NewLine;

    Dts.Variables[“ErrorMessage”].Value = Dts.Variables[“ErrorMessage”].Value.ToString() + ErrorMessage;
    Dts.Variables[“ErrorCount”].Value = (int)Dts.Variables[“ErrorCount”].Value + ErrorCount;
    Dts.Variables[“WarningCount”].Value = (int)Dts.Variables[“WarningCount”].Value + WarningCount;

    Dts.TaskResult = (int)ScriptResults.Success;
    }
    }
    }

    — Lastly 3rd script to send emails:

    /*
    Microsoft SQL Server Integration Services Script Task
    Write scripts using Microsoft Visual C# 2008.
    The ScriptMain is the entry point class of the script.
    */

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Collections.Generic;
    using System.Text;
    using System.Net.Mail;

    namespace ST_853127abc66b4055b278d162b50a2635.csproj
    {
    [System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

    #region VSTA generated code
    enum ScriptResults
    {
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

    /*
    The execution engine calls this method when the task executes.
    To access the object model, use the Dts property. Connections, variables, events,
    and logging features are available as members of the Dts property as shown in the following examples.

    To reference a variable, call Dts.Variables[“MyCaseSensitiveVariableName”].Value;
    To post a log entry, call Dts.Log(“This is my log text”, 999, null);
    To fire an event, call Dts.Events.FireInformation(99, “test”, “hit the help message”, “”, 0, true);

    To use the connections collection use something like the following:
    ConnectionManager cm = Dts.Connections.Add(“OLEDB”);
    cm.ConnectionString = “Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;”;

    Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    To open Help, press F1.
    */

    public void Main()
    {
    int x = 0;
    x = x + (int)Dts.Variables[“ErrorCount”].Value + (int)Dts.Variables[“WarningCount”].Value;

    // send alert only if errorcount + warning count > 0
    if (x >0) {
    string MailHost =Dts.Variables[“MailHost”].Value.ToString();
    string AlertSubscribers =Dts.Variables[“AlertSubscribers”].Value.ToString();
    string subject = “” ;
    string body = Dts.Variables[“ErrorMessage”].Value.ToString();
    subject = Dts.Variables[“PackageName”].Value.ToString() + “: ” + Dts.Variables[“ErrorCount”].Value.ToString() + ” Errors, ” + Dts.Variables[“WarningCount”].Value.ToString() + ” Warnings at ” + System.DateTime.Now.ToString(“dd-mm-yyyy hh:mm:ss”);

    //create the mail message
    MailMessage mail = new MailMessage();

    //set the addresses
    mail.From = new MailAddress(“DoNotReply@insight.com”,”SSIS Alert System”);
    mail.To.Add(AlertSubscribers);

    //set the content
    mail.Subject = subject ;
    mail.Body = body;
    mail.IsBodyHtml = false;

    //send the message
    SmtpClient smtp = new SmtpClient();
    smtp.Host = Dts.Variables[“MailHost”].Value.ToString();
    smtp.Port = 25;
    smtp.UseDefaultCredentials = true;
    smtp.Send(mail);
    }
    Dts.TaskResult = (int)ScriptResults.Success;
    }
    }
    }

    My email looks like this:

    From: SSIS Alert System [mailto:DoNotReply@insight.com]
    Sent: 08 July 2011 16:39
    To: Rickatson, John
    Subject: TestPackage: 2 Errors, 0 Warnings at 08-38-2011 04:38:41

    MachineName: ISHDT03271
    PackageID: {5DA41680-243F-4C31-B510-13A83B0C102F}
    PackageName: TestPackage
    ProductVersion: 10.0.2531.0
    – My Custom Error or Warning Message
    – My Custom Error or Warning Message

    as you can see the actual error message is missing sort of defeating the object!

    Any assistance you might offer would be greatly appreciated

    Thanks again

    John Rickatson

    mailto:john@rickatson.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s