Single mail on Failure

To get a single mail with an error message, when the package is failed. When we include a mail task on event handler (on Error) and if the package failed with 5 errors, we will receives 5 mails which would be confusing so to get a single mail on error following steps can be followed.

Step1:
Create a variable (ErrorDesc), which holds the error description (reason for the package failure), at a package level.

Step2:
Place the script task from tool box to Event Handlers: “Onerror”. Write a c# code to read the data from system variable: ErrorDescription, which has the error message when the package is failed, to user defined variable: ErrorDesc.

PFB for the c# Code.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_91e0631286c74800b32724e7bb492467.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
public void Main()
{
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
String s = Dts.Variables[“User::ErrorDesc”].Value.ToString() +“\n”+ Dts.Variables[“System::ErrorDescription”].Value.ToString();
Dts.Variables[“User::ErrorDesc”].Value = s.ToString();
}

Step3:
Create a mail task on Event Handler: OnTaskFailed. Enter the users mail id, to whom send the mail, against “TO” in the Mail tab and enter other fields like “From”, etc.

In the Expression Tab, Map the user variable: ErrorDesc, which has the error message against MessageSource.

After implementing the above, user receive the single mail for more than one error.

Advertisements

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