Tuesday, 6 August 2013

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:
  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










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

Close
































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).
Parameters























11) The result
Now execute the Execute SQL Task and watch your mailbox.

An email with html formatting






















If you don't like this solution, check out the Script Task solution or the third party tasks.

4 comments:

  1. Can you modify this to add an attachement?

    ReplyDelete
    Replies
    1. See MSDN for attachments (file_attachments). Just an extra parameter.

      Delete
  2. Hi.
    When I try to execute it from a ETL in SSIS in a SQL Task, it fails.
    The error I get it's the following:

    [Execute SQL Task] Error: Executing the query "EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ?..." failed with the following error: "ORA-00900: invalid SQL statement". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    ReplyDelete
    Replies
    1. When I see ORA-00900, I suspect you're using a TSQL Command on an Oracle connection.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...