SSIS - Using DB email to send dynamic HTML emails from SSIS

Article
1/17/2024

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.

Prerequisites:

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.

Mail Header

ID	CustomerName	Amount
1	Sudeep	100
2	Ankur	-10
3	Manju	20

**
**
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 Records

INSERT INTO ``[RAJ].[dbo].[tblCust]([CustName],[Amount])
    (
    ``SELECT ``'Sudeep'``,100
        ``UNION ``ALL
    ``SELECT ``'Ankur'``,-10
        ``UNION ``ALL
    ``SELECT ``'Manju'``,3 )

First we set up the Database Mail Profile with the following query in our SQL server.****

USE msdb

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 @EmailAddress = <'mailid@domain.com'>;

SET @DisplayUser = 'SQL Mail';

--The following section adds our Account, Profile, and Profile-Account association to the system.

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = @AccountName,

@email_address = @EmailAddress,

@display_name = @DisplayUser,

@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @ProfileName,

@account_name = @AccountName,

@sequence_number = 1 ;

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>' ``+
            ``CAST``((
        ``SELECT
                    ``td ``= ``ID, ``''``,
                    td ``= ``CustName, ``''``,
            ``'td/@bgcolor'``=``CASE ``WHEN ``Amount>0 ``THEN ``'Green'
            ``ELSE ``'Red' ``END``,
                    td ``= ``Amount, ``''
            ``FROM ``tblCust
                    ``FOR XML ``PATH(``'tr'``), ``TYPE``) ``AS NVARCHAR``(``MAX``)) + ``N'</table></body></html>'

-- Sends Mail

EXEC ``msdb.dbo.``sp_send_dbmail ``@recipients``=``'<MailID@maildomain.Com>'``,
    ``@subject ``= ``'SQL Errors Report'``,
    ``@body ``= ``@tableHTML``,
    ``@body_format ``= ``'HTML'``,
    ``@profile_name ``= ``'SudeepTest'``;

In this query the HTML body is formed in the variable @tableHTML, and the body format is set to ‘HTML’.

Depending on the number of records fetched in the query the HTML body that is created from the above query would looks like:

<html>

<body>

<h1>

Mail Header</ h1>

<td>

ID</ td>

<td>

CustomerName</ td>

<td>

Amount</ td>

</ tr>

<tr>

<td>

1</ td>

<td>

Sudeep</ td>

100</ td>

</ tr>

<tr>

<td>

2</ td>

<td>

Ankur</ td>

-10</ td>

</ tr>

<tr>

<td>

3</ td>

<td>

Manju</ td>

3</ td>

</ tr>

table>

</body>

</html>

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.

Prerequisites:

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.

Share via

SSIS - Using DB email to send dynamic HTML emails from SSIS

Mail Header

Additional resources