In the SSIS Forum I often find people asking if it’s possible to send mail in HTML format or Fetch data from Table and use it in the mail body. In the Send Mail Task we can only send the mails in text format or send the query result as an attachment.
Today I will explain the way this can be achieved.
If we have a table with columns as ID, CustName, Amount and we need to have the mail sent in the following format. The rows will be colored based on a particular condition which can be set in the query.
Source Table Script
CREATE TABLE [dbo].[tblCust](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CustName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Amount] [int] NULL
) ON [PRIMARY]
INSERT INTO [RAJ].[dbo].[tblCust]([CustName],[Amount])
SELECT 'Manju',3 )
First we set up the Database Mail Profile with the following query in our SQL server.
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
--Here I am setting up our Profile Name, Account Name, STMP server name, and the name that will display in the from field in the e-mail.
SET @ProfileName = 'Sudeep';
SET @AccountName = 'Sudeep';
SET @SMTPAddress = '192.168.1.101';
SET @DisplayUser = 'SQL Mail';
--The following section adds our Account, Profile, and Profile-Account association to the system.
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
@profile_name = @ProfileName
@profile_name = @ProfileName,
@account_name = @AccountName,
Next is the query that provides us our data in HTML format which will be used in the mail body.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<html><body><h1>Mail Header</h1>' +
N'<table border="1" width="100%">' +
N'<tr bgcolor="gray"><td>ID</td><td>CustomerName</td><td>Amount</td></tr>' +
td = ID, '',
td = CustName, '',
'td/@bgcolor'=CASE WHEN Amount>0 THEN 'Green'
ELSE 'Red' END,
td = Amount, ''
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'
-- Sends Mail
In this query the HTML body is formed in the variable @tableHTML, and the body format is set to ‘HTML’.
EXEC msdb.dbo.sp_send_dbmail @recipients='<MailID@maildomain.Com>',
@subject = 'SQL Errors Report',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'SudeepTest';
Depending on the number of records fetched in the query the HTML body that is created from the above query would looks like:
<table border="1" width="100%">
If you need any kind of modification in your table look and feel you could think about the related HTML that you need and modify the above query accordingly.
Now in the SSIS Package that you have use the 2nd query in Execute SQL Task and check the mail that you receive.
It is not necessary that it can be used for tables. You can modify the select statement in the above query to return the data as HTML format without FROM clause or simply put your HTML code in the @tableHTML variable.
- The SMTP server should be installed and configured
- The user in the profile should have access to the SMTP Server
- Check the SMTP server for the domains to which it can broadcast mails.