August 4, 2014 at 3:00 pm
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?
August 4, 2014 at 3:09 pm
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.
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]
August 6, 2014 at 12:28 pm
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.
August 6, 2014 at 12:56 pm
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.
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]
August 6, 2014 at 1:48 pm
Wouldn't that send the email regardless? Even if there weren't an error?
August 6, 2014 at 2:10 pm
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.
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]
August 12, 2014 at 7:24 am
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()
August 12, 2014 at 7:31 am
Can you show me the code that attempts to assign the message to the variable?
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]
August 12, 2014 at 7:38 am
/*
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;
}
}
}
August 12, 2014 at 7:42 am
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.
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]
August 12, 2014 at 7:57 am
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
August 12, 2014 at 8:04 am
Great. Now hope it never generates an email. 🙂
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