Sending query result from a table via email in SSIS in a table format
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 data with help of TAB's in String.Format.
But i have to get that in a table format.
Is it possible to create a mail message in script task and use html tags to display results in a table with the help of variables in the package?
Well i am new to vb.net and SSIS so might require a detailed solution.
Thank You!
February 17th, 2012 1:26am
You can do it using Execute SQL task...
Check the below screen shot...i am currently using this.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2008R2 Administrator',
@recipients = 'danw@Adventure-Works.com',
@query = 'SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder
WHERE DueDate > ''2006-04-30''
AND DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' ,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;
Mark as answer if the post help you... Regards, Indraneel A
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
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='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
Paste this code in Execute SQL.....that will work. Revert back in case of any issue.
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2012 1:32am
For using dbmail i need to have an account. Is there any special configuration required in the execute sql task to send the mail?
Currently connection type OLE DB
SQLSourceType is Direct Input
In SQLStatement i pasted the edited version of code from above.
February 17th, 2012 4:01am
In SSIS the task "Send Mail Task" is unusable. You need to create script task and connect to the smtp server, gmail account or another and then sent email.
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2012 6:07am
There are a few tasks on codeplex that might be helpful:
HTML Table Destination
Send HTML Mail Task
I've not personally used either, but as Todd is the author I trust that they do what they say in an efficient manner.Please mark answered posts. Thanks for your time.
February 17th, 2012 11:09am
Sent the html formatted query result using data table inside script task but the problem now is i have to enter my credentials.
Free Windows Admin Tool Kit Click here and download it now
February 20th, 2012 10:39pm
I'm getting the bellow error:
Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.
September 15th, 2012 3:28pm