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

  • Article

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

GO

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>

    <table border="1" width="100%">

        <tr bgcolor="gray">

            <td>

                ID</ td>

            <td>

                CustomerName</ td>

            <td>

                Amount</ td>

        </ tr>

        <tr>

            <td>

                1</ td>

            <td>

                Sudeep</ td>

            <td bgcolor="Green">

                100</ td>

        </ tr>

        <tr>

            <td>

                2</ td>

            <td>

                Ankur</ td>

            <td bgcolor="Red">

                -10</ td>

        </ tr>

        <tr>

            <td>

                3</ td>

            <td>

                Manju</ td>

            <td bgcolor="Green">

                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.