Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending scheduled SQL query results via HTML e-mail using SSIS


Sending scheduled SQL query results via HTML e-mail using SSIS

Author
Message
Paul Clancy
Paul Clancy
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 196
Comments posted to this topic are about the item Sending scheduled SQL query results via HTML e-mail using SSIS
Paul Clancy
Paul Clancy
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 196
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.
Paul Clancy
Paul Clancy
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 196
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.
nsawdon
nsawdon
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 145
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" ?
Paul Clancy
Paul Clancy
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 196
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. :-)
DEK46656
DEK46656
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 569
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"
Paul Clancy
Paul Clancy
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 196
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.
BuilderBits
BuilderBits
Mr or Mrs. 500
Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)

Group: General Forum Members
Points: 511 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!
Paul Clancy
Paul Clancy
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 196
It's included as an install option in SQL Server 2005 and SQL Server 2008, though not in the free Express or Compact editions.
Simon E Doubt
Simon E Doubt
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 892
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search