Home > SSIS Best Practices > SSIS – Using system variables in Send Mail Task

SSIS – Using system variables in Send Mail Task

“Send Mail Task” is not the most flexible way to send out emails from a SSIS package. See my previous blog “SSIS – Auditing Strategy Through Package Event Handlers” to see how to use a XML Task and a Script Task to send out HTML emails.

But there are also situations where we just want to use the straightforward Send Mail Task to send out a notification if an error occurs.

Goal

Send out an email notification when error occurred in a SSIS package; Need to include the error description and the error source.

Solution

Step 1: to capture the error, create a Send Mail Task in the OnError Event Handler at the package level.

image

Step 2: In the Send Mail Task, try not to hard code anything, instead, use the Expression window to create expressions for the task properties.

I’ve created expressions for 4 very basic properties for the Send Mail Task, i.e. FromLine, MessageSource, Subject, ToLine.

image

The Subject property captures the machine name, which can tell you which environment the error is from. The MessageSource property captures the error description and the error source. Finally, the ToLine is set to a user variable which holds the email address you want to send the email to.

Because this task is in the OnError even handler, you will only receive the email if your package failed.

If you are like me, and do not write expressions in SSIS package everyday, the syntax can be tricky. Here is the syntax for the MessageSource property:

"Error Desc: " + @[System::ErrorDescription] + " Source Name: " + @[System::SourceName]

Categories: SSIS Best Practices
  1. May 7, 2014 at 1:55 pm

    Thank you, this helped me better understand how to use this feature!

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: