Of the over 40 components in Task Factory the TF Advanced Email and SMS Task is one of my favorites. In this blog I am going to discuss the rich features this task offers and provide a walkthrough of setting this task up to send an email or SMS text message upon package failure.

If you like this blog and you want to try out a free trial of Task Factory you can find it here: http://pragmaticworks.com/Products/Task-Factory/TrialDownload

Feature Highlights

• Supports Email and SMS messages.
• Can send emails with attachments.
• Inbuilt HTML editor for HTML email.
• Allows use of variables as placeholders in the Email Body so variables can be automatically replaced when an email is sent!
• Expressions can be set on properties to make the package dynamic.

Note: We will be using SMTP connection information to set up our email connection manager. Generally a basic Bing/Google search will return the connection information required. For example Yahoo SMTP Connection info or Gmail SMTP Connection info.

Ok let’s get started with our walkthrough.

1. Please open up a new SSIS Package.
2. Next click on the Event Handlers tab at the top.
1. Don’t change the executable, by default it is set at the package level.
2. Also leave OnError for the Event Handler.

1. Next click on the hyperlink “Click here to create an ‘OnError’ event handler for executable ‘<Executable Name>’.

1. Now pull in the Task Factory Advanced Email and SMS task.
2. Next we will set up a new SMTP Connection Manager.
1. Select the drop down box next to SMTP Connection and then select “Create New Email Connection”.

1. On the general tab fill out the following information:
• Protocol Type: (Must be SMTP)
• Mail Server: (The Mail Server will differ by mail provider. I’m using Office 365 settings.)
• User Name:
• Password
2. On the advanced tab fill out the following:
• Server Port: (Once again changes per mail provider)
• Type of encrypted connection: TLS, SSL or NONE
• Timeout in Seconds: (Default is 60)
3. Finally test connection to validate and click ok to close.

Setting up SMS Providers and Emails

In this section I will demo how easy it is to set up the component to send either an SMS or Email message. Note all of this can be set up dynamically using variables and expressions!

1. Select the dropdown for “To:” and select either email or SMS. Here I selected email and then manually entered the email address.
• Please note you can send emails to multiple recipients by separating them with a semi colon ;.

1. Ok, next let’s override our email option and select SMS.
• First enter the phone number.
• Next select the provider (The “Provider” will be required for SMS messages.)
• Finally select the add Icon, see screenshot below:

Dynamically set the body of the email and subject using expressions:

In this section we are going to use expressions for the body and subject of the email. Note that expressions can be set on virtually every property of this component.

1. Select the “Expressions” tab found at the bottom left of the TF Advanced Email editor.

1. From the properties window select “Subject” from the drop down.
2. In the “Expression” box select the Ellipsis button to open the expression editor.
3. Here is where we can use the SSIS Expression language and variables to make our subject dynamic at run time. (See third Screenshot below)

1. Once you have completed your expression for the Subject click ok and that will close the expression editor. Now from the properties window select “Message” and once again select the Ellipsis button to open the expression editor.

Now when I execute the package if an OnError event happens in the package and only if this event occurs then I will receive an email or text message!

Here is the email I received upon failure of the package, pretty cool!

Thanks for looking!