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 @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.