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 = 'rajsudeep@gmail.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>'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.
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.
Great post - a nice detailed walkthrough. It's something I could have used a while back instead of fighting my way through the GUI trying to set it up.
ReplyDeleteThis way will work great for those that love T-SQL, and can't get extra stuff loaded onto the server without a lot of red tape.
Since I don't have those issues, I hated marking up my T-SQL with HTML tags, and I was familiar with building custom objects for SSIS, I built my own solution. Using the HTML Table Destination and the Send HTML Mail Task, I can do the above by dragging and dropping inside the SSIS designer.
To each his own - I'm just glad there are lots of options! Keep up the good posts.
Hello Sudeep,
ReplyDeleteIf possible can you please post ForLoop container, ForEach Loop container, and Tasks.
Many thanks Sudeep.
Kind regards
Raghu
Hi Raghu,
ReplyDeleteYou could check out the other post in my blog.
Dynamic File Handling