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.