How to consolidate all error messages to one email from a send email task?

  • I have an event handler for an ssis package with the message source set in an expression as follows:

    "The package " +"\""+ @[System::PackageName] +"\"" + " Task: " + @[User::FailedTask] + " failed with the errorcode: " + (DT_STR, 100, 1252) @[System::ErrorCode] +"" + @[System::ErrorDescription] +"The error handling was triggered at: " + (DT_STR, 100, 1252) @[System::EventHandlerStartTime] + "The instanceGUID is " + (DT_STR, 100, 1252) @[System::ExecutionInstanceGUID]

    I'm noticed that sometimes I have multiple emails for one error. For example, I had the following messages emailed due to one connection manager error:

    Email 1

    The package "HSCQNXTWarehouseLoad" Task: Member PlanInfo Load failed with the errorcode: -1071611876 SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SQLPROD2005.ServiceCoordinator" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    Email2

    The package "HSCQNXTWarehouseLoad" Task: Member PlanInfo Load failed with the errorcode: -1073450985 component "Service Coordinator" (2246) failed validation and returned error code 0xC020801C.

    Email 3

    The package "HSCQNXTWarehouseLoad" Task: Member PlanInfo Load failed with the errorcode: -1073450996 One or more component failed validation.

    Email 4

    The package "HSCQNXTWarehouseLoad" Task: Member PlanInfo Load failed with the errorcode: -1073594105 There were errors during task validation.

    Is there any way to consolidate these into just one email?

  • Create a variable to hold the text you want as the email body.

    Have the event handler(s) append the error messages to this variable.

    Add an email task at the appropriate place to send an email using the content of the variable as the body of the email.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • What Im finding is that only one task will fail but the error messages that would be all together in one job history view seperated by Error and End Error will come in seperate emails instead of one. For example:

    Instead of getting one email with the following:

    Executed as user: HNETNT\SQLISDEV. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4270.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:37:32 PM Error: 2014-08-05 14:37:35.37 Code: 0xC001402C Source: HSCQNXTWarehouseLoad Connection manager "DWHSQLDEV1.DW" Description: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted". End Error Error: 2014-08-05 14:37:35.37 Code: 0xC0202009 Source: HSCQNXTWarehouseLoad Connection manager "DWHSQLDEV1.DW" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D00E. COM error object information is available. Source: "HSCQNXTWarehouseLoad" error code: 0x8004D00E Description: "The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted". ". End Error Error: 2014-08-05 14:37:35.67 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "DWHSQLDEV1.DW". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:37:32 PM Finished: 2:37:35 PM Elapsed: 3.416 seconds. The package execution failed. The step failed.

    I get 4 as follows:

    1st email

    Executed as user: HNETNT\SQLISDEV. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4270.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:37:32 PM Error: 2014-08-05 14:37:35.37 Code: 0xC001402C Source: HSCQNXTWarehouseLoad Connection manager "DWHSQLDEV1.DW" Description: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted". End Error

    2nd email

    Error: 2014-08-05 14:37:35.37 Code: 0xC0202009 Source: HSCQNXTWarehouseLoad Connection manager "DWHSQLDEV1.DW" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D00E. COM error object information is available. Source: "HSCQNXTWarehouseLoad" error code: 0x8004D00E Description: "The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted". ". End Error

    3rd Email

    Error: 2014-08-05 14:37:35.67 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "DWHSQLDEV1.DW". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error

    4th email

    DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:37:32 PM Finished: 2:37:35 PM Elapsed: 3.416 seconds. The package execution failed. The step failed.

  • Don't send the email from the event handler.

    Add an email task that runs at the end of the processing and use what I recommended earlier.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Wouldn't that send the email regardless? Even if there weren't an error?

  • You can set restrictions on when it executes. What I've done before is check the length of the combined error messages. If the length was zero, then don't send the email.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I've tried implementing what you advised but when I try to set the error Description in a script task to an ssis variable of type string I get the following error:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Variable "User::CareAlertLoadError" assigned a DTS object of type "Microsoft.SqlServer.Dts.Runtime.Variable". Only DTS objects of type ForEachEnumerator can be assigned to variables. Other DTS objects are not allowed in variables.

    at Microsoft.SqlServer.Dts.Runtime.Variable.set_Value(Object value)

    at ST_698657b33d404f9389483d16af5ca517.csproj.ScriptMain.Main()

    --- End of inner exception stack trace ---

    at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

  • Can you show me the code that attempts to assign the message to the variable?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • /*

    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;

    namespace ST_698657b33d404f9389483d16af5ca517.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()

    {

    //Dts.Variables["CareAlertErrorCode"].Value = Dts.Variables["ErrorCode"];

    Dts.Variables["CareAlertLoadError"].Value = Dts.Variables["ErrorDescription"];

    Dts.TaskResult = (int)ScriptResults.Success;

    }

    }

    }

  • As I expected.

    Change Dts.Variables["CareAlertLoadError"].Value = Dts.Variables["ErrorDescription"];

    to Dts.Variables["CareAlertLoadError"].Value = Dts.Variables["ErrorDescription"].value;

    You're trying to pass the whole variable object instead of just its value.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Oops, that did the trick. Also updated the code to concat the error as such

    Dts.Variables["CareAlertLoadError"].Value = String.Concat(Dts.Variables["CareAlertLoadError"].Value, Dts.Variables["ErrorDescription"].Value);

    Thanks Alvin

  • Great. Now hope it never generates an email. 🙂



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 12 posts - 1 through 11 (of 11 total)

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