Creating a Solid SSIS ETL Solution: Part 4 – Email Task and Package Execution Report

Creating a Solid SSIS ETL Solution: Navigation
Part 1. – Package and Error Logging
Part 1. – Package and Error Logging (cont.)
Part 2. – Package Configurations
Part 3. – Package Template Overview
Part 4. – Email Task and Package Execution Report

Updated Sample Files

If you’ve been following along with this series, I just updated the sample files, mainly concerning the report and associated sprocs. Please re-download the zip for all the updated goodies.

Execution Information

Up to this point we’ve discussed creating a custom SSIS execution logging and error capture system, package configurations, and a general overview of my SSIS package template. There are two minor pieces to go over to complete this rudimentary ETL framework: the Email Task and a report for viewing SSIS execution results.

Email Task

The email task included in my package template is a very handy notification tool. Throughout package execution, each PackageLogDetail start and end (Data flow tasks) append resultant information to the SSISPackageLogResultMessageForEmail variable, which is then sent out in an email body via the Emal Task at the end of the package. The recipients of the email will receive a detailed breakdown of what flows the package processed and the general results. If an error occurs, the body will have all the successful flows followed by the name of the flow which crashed along with an error code and message. So not only does the email show the error, it lets the recipients know which flows processed successfully before the error occurred. This makes it easy to manually process the remaining files by putting only the unprocessed files into the source folder, and manually executing the agent job which fires the package. (A separate way of accomplishing this is to check the execution report to see which flows [files] were processed before the error occurred.)

(As a side note, the Global Container is not actually needed to ensure the Email Task fires. If the Email Task precedence constraint is set to ‘Completion’ or ‘Error’, it will fire regardless of the outcome of previous tasks. The Global Container can be removed if necessary.)

The Email Task in this package template is also very dynamic. Let’s look a little closer at the task in Edit mode:

Email Task Editor - Mail

As can be seen in the image above, we are basically building an email. Starting from top to bottom:

• Select your SMTP connection
• Anything can be placed in the from clause. I’ve chosen a static, fake email address so I can make email sorting rules.
• The To, CC, Subject, and MessageSource (body) are all filled by expressions, which we will discuss in the next paragraph.
• If you feel it helps, you can set the priority of the mail being sent.
• The Email Task can also send out an attachment. This can be virtually anything (Error log files, etc).

The Mail tab contains all the standard set up for an Email Task, but static information in these fields would make the email fairly generic and not very informative. This is where the Email Task in this package template shines. I’ve included all kinds of good info in the email sent out from this package. Let’s look at the expressions I’ve built:

Email Task Editor - Expressions

As can be seen there are a lot of goodies packed into Subject and MessageSource.

The Subject line is built to tell the receiver immediately which package is sending mail and what the result of the package was. The default values set up for the result variables for the package are set to ‘Failure’ (ResultTypeID 3), which means if the package fails before it can even write the start of the package log, the email will show up as a package execution failure. As the package enters the meat of the control flow, the result id is changed immediately to a success (ResultTypeID 1), so if all goes well, the package will report success instead of failure. Now should the package encounter some issues, such as a package system failure or business rule / system failures in a data flow, the result ids will be changed at that time to the appropriate results: 3 for a total package failure and 2 for bus or sys failures in data flow. The SSISPackageLogResultID allows the email to dynamically set the result text in the email subject line.

The MessageSource is more detailed, obviously. A lot of great information is contained here, which should allow the recipient to quickly zero in on the offending package and source of error. The MessageSource contains the following information:

• The name of the machine which executed the package. If the package was fired by a job, the name of the SSIS server which executed the package will be listed here. If you fire it from your dev environment (desktop for example) the name of your dev environment (desktop) will show here.
• The name of the package which was executed.
• The task responsible for sending the email (valuable if the package fails).
• Finally the SSISPackageLogResultMessageForEmail, which should contain the results of any successful data flows as well as error code and message, if a system error occured and the package failed to complete.

From this information, it is very easy to troubleshoot what happened to the package.

As a warning, the email task will not fire and so nobody will receive a failure email if the package does not at least begin to run. In my experience this can be caused when the package configurations set up in a package do not have corresponding configurations in the SSISConfiguration table or configuration XML file (a typo can misalign everything). Basically anything that causes the package not to be able to be initiated will cause no email to be sent. In my opinion if you want total assurance you will receive some kind of notification if there is an error, I would add a database email alert to the job responsible for executing the package. At least this way if a configuration(s) becomes misaligned you will get some kind of heads up that something is amiss. Another place to look for error information if the package fails to start is the job history.

SSIS Execution Report

A second, powerful way to receive information on your ETL system is via the SSIS Execution Report I created. This report takes a StartTime and EndTime as parameters, and returns all the package execution information stored in the SSIS package logging system for that period of time. The report in general is pretty self explanatory, but some of its best features are:

• Count of how many package executions happened during a month.
• Summarize the execution duration during the month (Good for trending – See if your package execution is growing normally or in an odd manner).
• Count how many rows of data were moved through the system. Also, a breakdown of which were successful, etc.
• Drill down into each package and data flow task to see more detail, even the rows which failed to convert!
• Show the error message associated with a package failure.

SSIS Execution Report

With this report, it should be fairly easy to troubleshoot package execution and data flow issues in order to expedite the move to developing a fix to issues. One thing I can’t stand is spending countless hours troubleshooting trying to find out what broke, when, and why. This report gives you all this information. A time saver for sure.

This report is fairly mundane. There are a lot of improvements which could be made, some of which are a parameter for result type, so a person can easily filter on only failed packages. Another would be a trending graph to show duration and row count growth over time. It would be nice to have these features, but they are outside the scope of this series.

Series Conclusion

If you’ve read all 5 posts, I want to thank you for spending the time checking this out. I hope I’ve shed some light on how to set up a solid ETL solution with SSIS. I know this framework has already helped me tremendously on my SSIS projects, including a new Data Warehouse I’m currently in the middle of developing.

As always if there are any questions, please comment and I’ll be sure to get back to you!

Example Files

Files and Test Data