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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics