February 16, 2012 at 10:08 pm
How to send query results extracted from an execute sql task and then send it via email by script task using HTML formatting?.
I was able to send the query results using execute sql task then a for each loop container inside which i had a script task where the results were (stored in variables) appended to a string.
Then i sent the string using send mail task.
By this method i was able to seperate the query results with help of TAB's.
But i have to get that in a table format.
February 16, 2012 at 11:21 pm
Here's an article on how to send an HTML formatted e-mail using SSIS:
You could create a table in HTML format and put your data in it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 17, 2012 at 3:03 am
HTML table format
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<head>' +
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
N'<h2><font color="#0000ff" size="4">Work Order Report</font></h2>' +
N'</head>' +
N'<body>' +
N' <hr> ' +
N' ' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th>
</tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks2008R2.Production.WorkOrder as wo
JOIN AdventureWorks2008R2.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2006-04-30'
AND DATEDIFF(dd, '2006-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='xyz@abc.com',
@profile_name = 'SQl',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 29, 2012 at 4:47 pm
Great code, JLivingston!!
September 17, 2014 at 7:31 pm
So the SQL Query within the code , the current code doesn't seem to execute the SQL to get some result out of the SQL , how do we fix this?
May 19, 2015 at 10:20 am
Firstly, thanks J Livingston for your excellent example. I was able to modify it for my use.
SSISDeveloper189155 (9/17/2014)
So the SQL Query within the code , the current code doesn't seem to execute the SQL to get some result out of the SQL , how do we fix this?
189155, the code worked fine for me. I replaced the td = parts with my column names, adjusted the rest of the query appropriately, and then updated the N' sections above the query with HTML that suited my taste. Also keep in mind that the table-header sections <th> need to be updated to match the columns you assign to the td items in the SELECT.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply