Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
MSBI 30
SSIS 27
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQLSERVER 8
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS - Using DB Mail to send HTML format mail

Mar 2 2010 12:00AM by Sudeep Raj   

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.

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.

Tags: SSIS, Execute SQL Task, MSBI,


Sudeep Raj
12 · 13% · 4306
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

4  Comments  

  • Hello,

    Its very useful stuff. Now i got into a situation where my column names changes dynamically. In such case how can i pass column names in the SELECT of HTML variable. Please help.

    http://beyondrelational.com/modules/2/blogs/106/posts/11108/ssis-using-db-mail-to-send-html-format-mail.aspx

    Thank you.

    commented on May 19 2013 7:33PM
    srk
    1641 · 0% · 12
  • You will need to create the select query as a string(dynamic SQL) and then execute it. This could be configured by passing the column names as parameters to sp or store the column names in table, or if it's all the columns of a table you could use system table to generate it.

    commented on May 20 2013 3:46PM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Sudeep ,

    Thanks for the blog .I stuck with a task . Kindly help me out from that .I am getting an Output from DB using OLE DB connection which needs to be present in the mail body that needs to be send to a particular DL. Can you please suggest me the components that i need to use and the queries i need to include in each component. I tried using the record set but the values are stored as object which i am unable to do typecast in the send mail task expression .

    Thanks Athrey

    commented on Aug 11 2013 6:18AM
    athrey
    2916 · 0% · 3
  • Hi Sudeep,

    Its a nice article. It really helped me. I was looking for the same requirement. Please keep posting such articles. Thanks again

    manjunath :) :)

    commented on Jan 22 2014 5:23AM
    manyaya
    3162 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"SSIS - Using DB Mail to send HTML format mail" rated 5 out of 5 by 1 readers
SSIS - Using DB Mail to send HTML format mail , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]