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’

                                                              ii.      clip_image002[8]

4.       Now you will see that your package should have the following at the top.

                                                               i.      clip_image004[8]

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.

7.       clip_image002[4]

 

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:

1.       user@domain.com

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.

1.       usertoemail@domain.com

                                                              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:

g.        clip_image004[4]

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

                                                            iii.      clip_image006[4]

                                                            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

                                                            iii.      clip_image008[4]

i.         Then click Ok 3 times to get out of the Send Mail Task Editor.

8.       Once complete it will look like the following:

a.        clip_image010[4]

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.

b.       clip_image012[4]

5.       You should also receive the email with the error as you configured above.

0 thoughts on “SSIS – Error Handling and Error emailing for Packages”

    1. +1 On this. My browser copied the quote characters incorrectly. Pasted in notepad and just replaced with the standard double quote. (“)

  1. 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”.

    1. 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.

  2. 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?

    1. 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.

      1. 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?

      2. 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.

  3. 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.

    1. 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.

  4. 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)?

  5. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *