SSIS - How to Email Error Messages From SSIS Package

Scenario: 

You are working as ETL Developer in a Firm and creating different types of SSIS Packages for them. The Firm want you to include the logic or part in your SSIS Package that if package fails because of any error , they want to receive an email with Error Code, Task Name which failed and Error Message Details.

Solution: 

To include this logic in our SSIS Package, we will be using Event Handler. We can use either Send Mail Task or dbo.sp_send_dbmail in Execute SQL Task. This example is using dbo.sp_send_dbmail. Let's start with step by step approach.

Step 1: 

Create three variables 

EnvironemntName : 

As you will be running your package on different environment, It is good idea to use this variable and change the value according to the environment in which you run the SSIS Package. 

MailProfile : 

Provide the name of mail profile. To get the profile name, you can use this query on SQL Server 

SELECT * FROM msdb.dbo.sysmail_profile

Recipients : 

List all of the recipients you want to send an email, separate them with ";". e.g. aamir@gmail.com;july@yahoo.com

Fig 1: Variables for Error Email in SSIS Package

Step 2: 

Create OLE DB Connection Manager. I have created connection to SQL Server Database and named it DB_Connection.

Fig 2: Create OLE DB Connection in SSIS Package

Step 3: 

You will have different Tasks and Transformation in SSIS Package, For this Demo, I am going to have Execute SQL Task and inside I will be using Select 1/0 to fail this Task so we can test our Error email part.

Fig 3: Setup Execute SQL Task to Fail in SSIS Package

Step 4: 

Let's go to Event Handler and then Drag Execute SQL Task and configure as shown below

Fig 4: Write Expressions in SSIS Package for Error Email information

Here is the expression that I have used, You can copy the same and make changes according to your requirements

"EXEC msdb.dbo.sp_send_dbmail

 @profile_name = '"+ @[User::MailProfile] +"', 

@recipients = '"+ @[User::Recipients] +"',"

+"

 @body = "+"'Hi All,"+"

Error Information is below

Error Code:" + (DT_STR,50,1252)@[System::ErrorCode] +"

Task Name:"+ @[System::SourceName] +"

Error Description:" + @[System::ErrorDescription] +

"Thank you

IT Team"

+"',

@subject = '"+  @[System::PackageName] + " failed in  "+ @[User::EnvironemntName]+"'"

Step 5:

Execute the SSIS Package, The package will fail as 1/0 is infinity and our Execute SQL Task in Control Flow will throw an error. Once the Task will fail in Control Flow , SSIS Package will jump to Event Handler. In Event Handler we are sending email by using Execute SQL Task on Package Error. We should receive an email as shown below

Fig 5: Error Email from SSIS Package after Package failed