SSIS - How To Handle Multiple Emails From SSIS Package On Error


I have created an SSIS Package and  have configured Event Handler on Package level. If any error occurs in any Task, I want to Send Email with Error Code , Error Description etc. It is working great. But each time SSIS Package fails, It send more than one email. How to get only single email if error occurs in SSIS Package?


If the Event Handler is configured on package level OnError Event, If any Task fails then sequence of calls will be send to Event Handler. Let's say we have Data Flow Task inside the Sequence container. If Data Flow Task fails then first call will be send to Event Handler and then second will be Send by Sequence Container. That is the reason Tasks inside Event Handler will run multiple times in our case Send Email.

To handle this, we will create a variable "ErrorCnt" of Integer type. We will have value 0 for this variable and after Sending Email in Event Handler we will increase the value of variable from 0 to 1. We will use this variable in Precedence constraint to Send Email Task or any other task only if the value is 0. As the value will be increased after first call from 0 to 1. Next time the tasks will not run.

Step 1:
Create an SSIS Package, I have created SSIS Package with two Data Flow Tasks inside Sequence Container as shown below
Fig 1: Data Flow Tasks inside Sequence Container in SSIS Package

Step 2: 
Create the variables as shown below. I have created different variables those I have used in Event Handler to Send Email by using Execute SQL Task.
Fig 2: Create Variables in SSIS Package
The one variable we will be using to handle Multiple emails or execution of Tasks is ErrorCnt, set the value of this variable =0 as shown in Fig 2.

Step 3:
Let's go to Event Handler Pane now and configure. Bring the Script Task to Event Handler Pane. We will be using the Script Task as dummy. The only purpose is to set the Precedence Constraint so the below Tasks run only when ErrorCnt=0. I have used Execute SQL Task to Send an Email. Connect Script Task to Execute SQL Task. The last Task is going to be Execute SQL Task in which  we are going to change the value of ErrorCnt variable from 0 to 1.

Fig 3: Event Handler in SSIS Package

In Fig 3, I have showed you that how your Event Handler would look like. Now lets see how did I configured them. If you see the very first item that I have configured is Precedence Constraint between Script Task and Execute SQL Task(I am using this to send an email). I am putting condition ErrorCnt=0, That means it will run only when the value of ErrorCnt=0. Here is how it is configured

Fig 4: Configuring Precedence Constraint to use ErrorCnt Variable

Let's change the value of ErrorCnt variable from 0 to 1 so multiple calls can be handled.
Fig 5: Set the value of variable ErrorCnt to 1

Fig 6: Save 1 to ErrorCnt variable

You are all done. This sound lengthy package as I had to create some Data Flow Tasks etc. but it is pretty short and simple. You are creating a variable with value=0 and that is begin used in Precedence Constraint and let the other tasks run when value is 0. Once the Tasks are run in Event Handler , you set the value of variable to 1 so on next iteration, it will make the expression false and no Task will run. If you need to see how to send email, check this post.