Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Sending scheduled SQL query results via HTML e-mail using SSIS Expand / Collapse
Author
Message
Posted Tuesday, April 22, 2008 11:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 7:31 AM
Points: 43, Visits: 185
Comments posted to this topic are about the item Sending scheduled SQL query results via HTML e-mail using SSIS
Post #489051
Posted Wednesday, April 23, 2008 1:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 7:31 AM
Points: 43, Visits: 185
I spotted an error in the last line of the SQL query listed in the article: the string should also be tagged with '<NoRecords></NoRecords>'.

So the whole script should read as follows:

declare @v_CurrentDate datetime
set @v_CurrentDate = '2003-07-17'

if exists (select 1
from Sales.SalesOrderHeader
where OrderDate = @v_CurrentDate)
begin

select top 10 oh.OrderDate,
(select round(sum(TotalDue), 2)
from Sales.SalesOrderHeader
where OrderDate = @v_CurrentDate) as DayTotal,
p.ProductID, p.Name,
round(sum(oh.TotalDue), 2) as ProductSubtotal
from Sales.SalesOrderHeader oh
join Sales.SalesOrderDetail od
on od.SalesOrderID = oh.SalesOrderID
join Production.Product p
on p.ProductID = od.ProductID
where oh.OrderDate = @v_CurrentDate
group
by oh.OrderDate, p.ProductID, p.Name
order
by 5 desc, p.ProductID asc
for xml auto, elements, type, root('Order')

end
else
select cast('<NoRecords>No sales records available for this date.</NoRecords>' as xml)

I'll correct this in the main article if possible.
Post #489082
Posted Wednesday, April 23, 2008 1:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 7:31 AM
Points: 43, Visits: 185
The SQL code in the downloadable script file (link at the bottom of the article) is fine, it's just the listing in the article that was faulty. A correction has been submitted and I hope that it'll be posted soon.
Post #489085
Posted Wednesday, April 23, 2008 3:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:26 AM
Points: 7, Visits: 111
Is it possible to email the order totals content as a password protected email attachment in the Script Task "Send the Sales details via e-mail" ?
Post #489154
Posted Wednesday, April 23, 2008 4:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 7:31 AM
Points: 43, Visits: 185
Interesting question. Shouldn't be too difficult...one possible approach might be to add an 'Execute Process Task' control flow container calling the likes of WinZip with command-line parameters to encrypt the HTML file created in the 'Test output to HTML file' step. You'd then need to save the path and name of the zip file thus created as a new variable (for instance, varZipAttachment).

Then, in the Script Task add varHTMLMail.Attachments(varZipAttachment), set varHTMLMail.IsBodyHtml = False instead of true as at present and remove the references to the varDeleteListHTML variable and use a hard-coded introductory text string instead as the body text.

This is probably also possible directly in VB.NET script without relying on the external executable, but this isn't my specialty, I'm afraid.
Post #489164
Posted Wednesday, April 23, 2008 7:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 20, 2014 9:41 AM
Points: 353, Visits: 423
Nice article: I've had to create HTML emails from SS for a while and have had to do all of my work in stored procedures.

I'm curious, have you considered doing the XML/XSL-T work in the script task instead? If the code was all self contained in the task, then a more generic use of it would expand the possible application. Imagine a FOR EACH task looping through a series of queries that had to send a series of different emails to different end users. The loop could collect the content (XML) and based on what that content was grab the XSL needed. Pass those two variables into the script (along with To, Cc, etc) and let the task do the rest.

I wonder which approach would be less resource intensive?

Anyway, thanks for the article, it will prove helpful in the near future.


Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
Post #489278
Posted Wednesday, April 23, 2008 8:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 7:31 AM
Points: 43, Visits: 185
Thank you for your kind comments.

That sounds like a good approach if you really want to use SSIS as a basis for mailing all sorts of result sets all over the place and to different mailing lists, with different content, etc. I'm inclined to think though that if you're going to go down that kind of large-scale and flexible user reporting road you'd be better off using the features of Reporting Services (for instance) instead of re-inventing the wheel in SSIS.

I haven't done any investigating on the performance implications, I'm afraid...it's quite a simple example, but it just illustrates a way of extending SSIS instead of being a recommendation for a data mailing solution. It wouldn't surprise me at all if it could all be done a lot faster in code...but then you could just write a .NET app to connect to the source db, perform the transformations and mail them on, and bypass SSIS completely.
Post #489302
Posted Wednesday, April 23, 2008 8:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 6:38 PM
Points: 501, Visits: 251
Where do we get the SSIS ?

Is this a download from somewhere are is it part of VS or SQL?

Thanks

ERIK


Dam again!
Post #489321
Posted Wednesday, April 23, 2008 8:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 06, 2014 7:31 AM
Points: 43, Visits: 185
It's included as an install option in SQL Server 2005 and SQL Server 2008, though not in the free Express or Compact editions.
Post #489338
Posted Wednesday, April 23, 2008 8:46 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:13 PM
Points: 290, Visits: 726
Great article - thanks for the terrific contribution, Paul.
I'm going to be able to use the article immediately to help me get data out to the people who need it - they're going to love this!
Post #489341
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse