ustcer: Sending mail within SSIS - Part 3: Execute SQL 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:

Send Mail Task
Script Task with SmtpClient Class
Execute SQL Task with sp_send_dbmail
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

C) Execute SQL Task
The Execute SQL Task solution uses a stored procedure from SQL Server. To use that you first have to configure database mail in SSMS.

1) Database Mail Wizard
Open SQL Server Management Studio (SSMS). Go to Management and then to Database Mail.

Database Mail

2) Enable Database Mail
If Database Mail isn't available it will ask for it. Choose the first option to create a profile.

Enable Database Mail and create profile

3) Create Profile
Enter a name and description for the mail profile. You will need the name in the stored procedure later on.

Create a mail profile

4) New Database Mail Account
Click the Add button to create a new database mail account. This is where you configure the SMTP server and the FROM address.

Configure SMTP and FROM address

Account ready, continue wizard

5) Public / Private
Make your profile public (or private)

Public profile

6) System Parameters
Configure the System Parameters like max attachment size.

Configure System Parameters

7) Finish wizard
Now finish the wizard and go back to SSIS / SSDT.

Finish

8) Add OLE DB Connection Manager
Add an OLE DB Connection Manager and connect to the server where you configured DatabaseMail.

OLE DB Connection Manager

9) Add Execute SQL Task
Add an Execute SQL Task to the Control Flow or an Event Handler. Edit it and select the new connection manager. In the SQLStatement field we are executing the sp_send_dbmail stored procedure with some parameters to get the, subject, body and from address from the SSIS variables.

' Stored Procedure with parameters

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SSIS Joost Mail Profile',

    @recipients = ?,

    @subject = ?,

    @body = ?,

    @body_format = 'HTML' ;

sp_send_dbmail in SSIS

10) Parameters
Go to the Parameter Mapping pane and add the SSIS string variables as parameters. We can't configure the FROM address because we did that already in SSMS (Step 4).