April 22, 2008 at 11:40 pm
Comments posted to this topic are about the item Sending scheduled SQL query results via HTML e-mail using SSIS
April 23, 2008 at 1:36 am
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.
April 23, 2008 at 1:42 am
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.
April 23, 2008 at 3:56 am
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" ?
April 23, 2008 at 4:22 am
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. 🙂
April 23, 2008 at 7:48 am
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"
April 23, 2008 at 8:08 am
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.
April 23, 2008 at 8:32 am
Where do we get the SSIS ?
Is this a download from somewhere are is it part of VS or SQL?
Thanks
ERIK
Dam again!
April 23, 2008 at 8:43 am
It's included as an install option in SQL Server 2005 and SQL Server 2008, though not in the free Express or Compact editions.
April 23, 2008 at 8:46 am
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!
April 23, 2008 at 9:01 am
Thank you, sir! Glad to be of assistance.
April 23, 2008 at 9:04 am
Good lord...
I have it and did not realize it.
I never spend much time on the data server.
Erik
Dam again!
April 24, 2008 at 12:43 am
Very good overview article, Paul.
I'm glad to see I'm not the only one writing about this stuff.
http://www.sqlservercentral.com/Authors/Articles/David_McKinney/155294/
I look forward to seeing what else you come up with.
David McKinney.
April 24, 2008 at 4:36 am
Thanks, David...I wish I'd seen your excellent XSL article back when I was figuring out how to get all this working; it would have saved me a lot of time. If you're interested there are a few more similar articles posted on my website at http://www.360data.nl/EN/News.aspx
April 24, 2008 at 11:36 am
is any way to get the same result but using SQL Server 2000, i don't know if there is a native tool or a third party one.
Ia have some reports, a lot of data i could distribute in html format, but actually i cant create an HTML file from a DTS.
Thanks in advance... 🙂
Sorry about my english.. is not as good as I'd wish..
Viewing 15 posts - 1 through 15 (of 84 total)
You must be logged in to reply to this topic. Login to reply