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

  • Comments posted to this topic are about the item Sending scheduled SQL query results via HTML e-mail using SSIS

  • 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)


    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


    by oh.OrderDate, p.ProductID, p.Name


    by 5 desc, p.ProductID asc

    for xml auto, elements, type, root('Order')



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

    I'll correct this in the main article if possible.

  • 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.

  • 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" ?

  • Interesting question. Shouldn't be too 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. 🙂

  • 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"

  • 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'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.

  • Where do we get the SSIS ?

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



    Dam again!

  • It's included as an install option in SQL Server 2005 and SQL Server 2008, though not in the free Express or Compact editions.

  • 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!

  • Thank you, sir! Glad to be of assistance.

  • Good lord...

    I have it and did not realize it.

    I never spend much time on the data server.


    Dam again!

  • Very good overview article, Paul.

    I'm glad to see I'm not the only one writing about this stuff.

    I look forward to seeing what else you come up with.

    David McKinney.

  • 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

  • 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