SSIS – Error Handling and Error emailing for Packages
What the steps below do is to handle error handling for your SSIS packages. And then within the error handling enable it so that you can email the exact error to email addresses so that they are notified when there is an error and what the error was.
Example:
· In our example we are going to be using a package named: SSAS-DroppingPartitions
· This is what will be referenced below.
Enabling Error Handling in an SSIS Package
1. Open your SSIS Package
2. Then click on Event Handlers at the top.
3. Then click on:
a. Click here to create an ‘On Error’ even handler for executable ‘Package Name’
b. EG
i. Click here to create an ‘On Error’ even handler for executable ‘SSAS-DroppingPartitions’
4. Now you will see that your package should have the following at the top.
5. Now your package is ready for specific event Handlers to be added.
6. Below is what it will look like with the Event Handler Enabled
Enabling your package error to be emailed
Adding an SMTP – Connection Manager for the Send Mail Task
1. The first thing that you need to do is to setup a new connection which will enable you to send the actual emails by doing the following below.
2. Right click in the Connection Managers window and select the following:
a. New Connection.
3. Then click on:
a. SMTP Connection manager for the Send Mail task
4. Then click Add
5. This will then open the SMTP Connection Manager Editor Window and you will need to fill in the following:
a. Name:
i. SMTP-Mail.Mailserver.com
ii. NOTE: This is the name of our mail server which we are sending the email.
b. Description:
i. SMTP-Mail.Mailserver.com
c. SMTP Server
i. Mail.Mailserver.com
ii. NOTE:
1. This is the actual DNS address for your SMTP Server
d. Tick
i. Use Windows authentication.
ii. NOTE:
1. This is because in our current setup the mail server requires Windows Authentication in order to send out emails.
e. Then click Ok.
6. Now you will see your SMTP connection in your connection manager’s window.
Adding the Send Mail Task and configuring the Send Mail Task
1. What we are going to do is to configure the send email task to send email to the people who require the email. And then configure what gets send out as part of the email.
2. Click in the Toolbox and drag the Send Email task into your Event Handler Window
3. Right click on the Send Mail Task and click on Edit
4. Under General put in the following for the Name and Description
a. Send email if Task Fails
5. Then click on Mail in the left hand side.
a. Where it says SmtpConnection click on the drop down and select the SMTP connection that you created in the section called Adding an SMTP – Connection Manager for the Send Mail Task above.
i. EG:
ii. SMTP-Mail.Mailserver.com
b. Where it says From:
i. This must be a valid FROM address that the mail server receiving the required email to relay will accept.
ii. EG:
2. NOTE: In some mail server setups you will have to ensure that the from email address is allowed to replay via your mail server.
c. Where it says To:
i. This is to whom you want to send the emails to.
ii. Where it says Subject I put in the following:
iii. SSIS Error: Package Name
iv. EG:
1. SSIS Error: SSAS-DroppingPartitions
d. Where it says MessageSourceType this must be left as the default which is:
i. Direct Input
e. We will configure the Message Source in the next step.
f. So once complete for now it will look like the following:
6. Next is where we are going to dynamically input our error message and details per SSIS package using System Variables and expressions.
7. To configure the Message Source type click on the Expressions in the left hand side.
a. Click on the plus sign next to Expressions
b. Then click on the Ellipses button, this will open the Property Expressions Editor
i. Click under Properties and click on the drop down button.
ii. Select the following:
1. Message Source
iii. Then once again click on the Ellipses button.
c. This will open the Expression Builder
d. Now this is where you will add the following into your Expression builder, an explanation will be below.
“Package: “+ (DT_WSTR, 50) @[System::PackageName] +”.
Time: ” + (DT_WSTR, 50) @[System::StartTime] +”.
Task: “+ (DT_WSTR, 50) @[System::SourceName] +”.
Error Description: ” + (DT_STR, 2000,1252) @[System::ErrorDescription]
i. NOTE: If you want to format your text, use Notepad and then copy and paste it from notepad into your Expression Window.
e. What the above does the following:
i. It starts with the Package name
1. And then the package Name variable.
ii. Then it is the time of the error
1. And then the StartTime variable.
iii. Next is the Task Name
1. With the TaskName Variable
iv. Finally is the Error Description
1. With the ErrorDescription variable
2. NOTE: Because the error is text you have to change the data type from the default which is DT_WSTR to DT_STR
a. With this you then need to add the length and then also the code page
i. In our example which works is the code page 1252
f. Then click on Evaluate Expression
i. This should then come back with the expression in the Expression Value window above the Evaluate Expression button.
ii. Below is what the Expression looks like
iv. Click Ok.
g. Now to add an expression for our subject click on Property drop down in the Property Expressions Editor.
i. Select Subject
1. Then once again click on the Ellipses button.
ii. This will open the Expression Builder
iii. Now this is where you will add the following into your Expression builder, an explanation will be below.
“SSIS Error: ” + (DT_STR, 50,1252) @[System::PackageName]
1. What this does is just takes the Package Name and puts it into the subject line in our email.
h. Then click on Evaluate Expression
i. This should then come back with the expression in the Expression Value window above the Evaluate Expression button.
ii. As shown below is what it looks like
i. Then click Ok 3 times to get out of the Send Mail Task Editor.
8. Once complete it will look like the following:
b.
Testing your Send Mail Task
1. The final step is to test to make sure that the Send Mail task works.
a. NOTE: I would only do this for the first Send Mail task, because if this works it should work for all other packages as well.
2. Go into your Control Flow and deliberately change a task so that it fails.
a. NOTE: In our example I changed an Execute SQL task, and then change the Stored Procedure name so that I knew it would fail because the Stored Procedure does not exist.
3. Once you have saved your change then run the package in Debug mode.
4. When the package runs it will FAIL which is what we want.
a. Now if you go and look in the Event Handlers Window you should see your Send email if Task Fails as green.
5. You should also receive the email with the error as you configured above.
Excellent article. Very useful and thank you..
Great, thanks it helped.
i tried this but i was having problems with the message source expressions, it didnt like it and errored
Hi there
I think just ensure that the single and double quotes are correct when you are copying and pasting.
If possible to send me a screenshot of the error, that might help.
Thanks
Gilbert
+1 On this. My browser copied the quote characters incorrectly. Pasted in notepad and just replaced with the standard double quote. (“)
I have a question. Only one task failed. But it sent two error emails. This was a dataflow task that failed.
Email 1:Package: DQSales_Load.Time: 3/25/2015 1:53:17 PM.Task: Send Mail if Task Fails.Error Description: component “Flat File Source History” (7341) failed the pre-execute phase and returned error code 0xC020200E.
Email 2:Package: DQSales_Load.Time: 3/25/2015 1:53:17 PM.Task: Send Mail if Task Fails.Error Description: Cannot open the datafile “D:SSIS PackagesDataQuickSourceRCA_History.TXT”.
Hi there,
From the above error, the reason for the two emails, is because there were two errors when the task failed. One notifying you that it Flat File Source History failed. As well as that it could not open the datafile called “D:SSIS PackagesDataQuickSourceRCA_History.TXT”
This would be as expected, due to the send email task sending emails on error, of which there were two above in your package.
Also the task name is the name of the send mail task rather than the task which failed. Is there a way to fix this?
Hi there, if I understand your question, that is expected due to it using the Task Name is the variable used for the name when sent in the email.
You could look for other system variables in SSIS, and use that as the name. I preferred to use the Task Name, so that you would know which task failed, and from the error message contained within the body of the email, this would enable you to know exactly where to start looking on how to resolve the error.
The issue is that the Task Name is actually the “Send Mail” task, not the actual task name that failed. If you look at Niveditha’s post with the fail messages, you’ll see that the task name is “Send Mail if Task Fails”. The task name should actually be the name of the task that failed. Any idea how to display the actual failed task name in the email?
Great post, helped quite a bit!
There is one issue I found when implementing. The Task Name that gets emailed is actually the “Send Mail” task, not the actual task name that failed.
If you look at Niveditha’s post with the fail messages, you’ll see that the task name is “Send Mail if Task Fails”. The task name should actually be the name of the task that failed.
To accomplish this, replace the variable @[System::TaskName] with @[System::SourceName] in the “Message Source” expression.
Hey there David, you are indeed correct.
Thanks for pointing that out. I have updated the blog past also.
Thank you so much for the response!
Thanks . It was very useful .
Pleasure, I am glad it helped you.
Hi I am processing a loop of files inside a folder and two files among 10 were failed i just want to send a mail stating that those two files were not processed.
Hi there
The way the error handling currently works is it will email the first error that it gets in the error log.
If you wanted to know exactly which files have failed, I would then suggest having a precedence constraint for errors, which would error to a file or SQL Table. After which you could then have an email task process, and if there are any rows in this table, to then email you what those errors are.
I hope that, that helps.
Hi Gilbert,
a great article! Exactly that what I was looking for.
Thank you very much – Sascha
Hi Sascha,
Glad it was of help
Regards
Gilbert
how exactly sending an email with multiple error messages? instead of per error message?
I am not sure if that can be done.
hmmm… okay.. by the way this is a good article. (Y)
Thanks
Hi Gilbert, a lot of times they helped posts, thank you. I wanted to ask watching this post, if there is a way to keep the number of errors at the level of the package in the database (I use SQL Server 2014)?
Hi there, I am sure that there would possibly be, you would need to store the number of errors as a variable and then write this back to your database.
Another option would be to query the SSIS catalog DB?
Thank you so much for this post. Was a lifesaver for a project I am working on this morning. Explained exactly what I needed to do.
Glad that it helped you solve your issue.
Great article…I cannot find SourceName and ErrorDescription in dropdown of Variables and Parameters though.
Hi there, they should be there as far as I can see. I recently used it without any issues
Found it, I didn’t set the Send mail task under The OnError Evenhandler
Glad you found it and got it working.
Everythin is working fine, except that event handler does not execute when the data flow fails. Am i missing some kind of connection between data flow and event handler?.
Hi there,
Ensure that you are in the “Event Handler” screen before clicking to add the OnError Send email task
Hi Gilbert,
My SSIS package works fine as well but event handler do not send email.
I have also changed property delay validation to true.
On package execution results event handlers are not called? Any idea why is that? Send mail task executes fine when I test as a stand alone task.
Thanks,
Sapna
Hi there, the goal is for it to only send the email when there is an error.
So if the package is successful then it will not send an email.
Hello! I’m working on a package that calls a lot of other packages via the Execute Package Task. So I have a main package (MainControlPackage) that calls let’s say 2 other packages (OneLoadPackage, TwoLoadPackage.) Is it possible to have the e-mail specify the Load sub packages in the error e-mail instead of the MainControlPackage?
When I use the below code, the “PackageName” variable is always the MainControlPackage. I would like there to be the ability to display the OneLoadPackage/TwoLoadPackage if one of them fails. I figure I could probably setup a ProjectParameter in each of the load packages and assign a variable to it at the start of each package, but would rather avoid that if I’m missing something easy. Thanks for your help and this article was awesome!!
“Package: “+ (DT_WSTR, 50) @[System::PackageName] +”.
Start Time: ” + (DT_WSTR, 50) @[System::StartTime] +”.
Container Start Time: ” + (DT_WSTR,50) @[System::ContainerStartTime] +”.
Task: “+ (DT_WSTR, 50) @[System::SourceName] +”.
Error Description: ” + (DT_STR, 2000,1252) @[System::ErrorDescription]
HI there
I think that it should work in the way you describe.
What I sometimes do is I put in the emailing at the Parent and child packages to ensure that nothing is missed.
I would rather get 2 email failures than none.
buen mesnaje
si quiero capturar la advertencia ya que hay esta mas claro el error
Hi,
I am getting an error while executing the package “Error: Failed to lock variable “The Package failed with the below Error:
Error Source : Dynamic file creation
Error Code : -1073548540
Error Description : An error occurred with the following error message: “Could not find file ‘Folderpathname’.”.
” for read access with error 0xC0010001 “The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.”.
“
Hi Monish,
Maybe remove the variable “FolderPathName” and see if that fixes your error.