Tuesday, December 5, 2017

How to Send Error Email with Error Code, Error Description by using Send Mail Task in SSIS

Send Mail Task to Send Error Email with Error Code, Error Description in SSIS
Scenario :
Suppose we have an SSIS Package with some tasks. Whenever if there is any Error occurred during the Execution of the Package , then it should send and Email with the details of the Task Name at which Error Occurred, Error Code and Error Description to the Developer. 
Suppose We have a Package which has failed during run time as follows...
We can handle this Scenario by Using the Send Mail Task at the Event Handlers with for the Event OnError , as follows...
Here in the below Example I am using Gmail SMTP Server ( smtp.gmail.com ) for Send Mail task configuration.
------------------------------------------------------------------------------
Phase I : Create the Required Package Level Parameters
------------------------------------------------------------------------------
Here I have Create 3 Parameters which I will use them in Send Mail Task Configuration.
pEmailFrom : ( From Email Address value, eg. mail*******@gmail.com)
pEmailTo : ( To Email Address value, eg. mail*******@gmail.com )
pEnvironment : ( The Environment of the Package running, eg. Production )


------------------------------------------------------------------------------
Phase II : Create the SMTP Connection
------------------------------------------------------------------------------
Here I have used Gmail (smtp.gmail.com) to create the SMTP Connection for Send Mail Task Configuration.


------------------------------------------------------------------------------
Phase III : Configuration of Send Mail Task in Event Handlers (for OnError Event)
------------------------------------------------------------------------------
Here I am Configuring the Send Mail Task for the OnError Event at Package Scope.


A) Add the Send Mail in Event Handlers Section.


B) Go to Send Mail Task Editor > Mail Tab, and Set the Connection


C) Go to Send Mail Task Editor > Expressions Tab, and Set Expressions
Here you configure the Email with Expressions, using System Variables, Parameters and Custom messages as shown below example.

FromLine : @[$Package::pEmailFrom]
ToLine : @[$Package::pEmailTo]
Subject :
"The Package "+@[System::PackageName]+" failed in "+ @[$Package::pEnvironment]
Message Source :
"Hi Team,"+
"The Package "+ @[System::PackageName]+" has failed in "+ @[$Package::pEnvironment] +" with the below Error."+
"Error Code : "+(DT_WSTR,50)@[System::ErrorCode]+
"Error Description : "+@[System::ErrorDescription]+
"Error Source: "+@[System::SourceName]+
"Please lookinto the issue and fix the same"+

"Thanks"+
"SSIS System"
Next Say Ok.

Now the Send Mail Task is Ready, which will send an Email with Error Details whenever the Package got an Error ( if you configured correctly )


Note : This is Just an example which gives an Overview of How to Configure the Send Mail task for OnError Event. You need to use the Proper SMTP Server details to work this Task Correctly.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.