Sending mail within SSIS - Part 1: Send Mail Task

Case
I want to send mail within SSIS, preferably HTML formatted. What are the options?

Solutions

There are a couple of solutions to mail within SSIS:

1. Send Mail Task
2. Script Task with SmtpClient Class
3. Execute SQL Task with sp_send_dbmail
4. Custom Tasks like Send HTML Mail Task or COZYROC

To demonstrate the various solutions, I'm working with these four SSIS string variables. They contain the subject, body, from- and to address. Add these variables to your package and give them a suitable value. You could also use parameters instead if you're using 2012 project deployment.

Add these four variables to your package

A) Send Mail Task
This is the standard task within SSIS to send mail. Good for simple plaintext emails but there are a couple of drawbacks. First see how it works.

1) SMTP Connection Manager
Right click in the Connection Managers window and add an SMTP Connect manager. Add the SMTP server. This is the first drawback. The settings are very limited. Things like port or credentials can't be set.

SMTP Connection manager

2) Send Mail Task
Add the Send Mail Task to the Control Flow (or to an event handler). Give it a suitable name and on the Mail pane at SmtpConnection, select our new Connection Manager.

Select SMTP Connection Manager

3) Expressions
After selecting the Connection Manager (leave rest unchanged) go to the Expressions pane and add an expression on the subject, body, to and from. Use the variables to overwrite these fields. After this click OK to close the editor and test it.

Expressions

4) Testing
Now run the task and check the mailbox. Now you will see the second drawback. The Send Mail Task doesn't support HTML formatted mail. So only plain text.

No HTML Formatting

If you want to overcome the two drawbacks then you have to use one of the other solutions. Next example solution: Script Task