TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
SSIS - How to Email Error Messages From SSIS Package
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.
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.
Create three variables
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.
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
List all of the recipients you want to send an email, separate them with ";". e.g. firstname.lastname@example.org;email@example.com
Fig 1: Variables for Error Email in SSIS Package
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
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
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
@subject = '"+ @[System::PackageName] + " failed in "+ @[User::EnvironemntName]+"'"
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