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

  • Michael Jenck (6/4/2008)

    I had a similar problem. We tried to use a network account to run the SQL server agent. At first it crashed the server. Once we set the account as a a member of the correct local group - it worked fine. Our case required two sets of permissions local and network.

    my 2 cents.

    When you say local group, do you mean on the server where the db is? I imagine it's some sort of permissions thing. The package runs with my credentials. I have another package, that doesn't send email, that runs just fine using my creds. Funny thing is both packages have file connections to the same directory on the network. However, the package that doesn't run, goes one folder deeper.

  • I tried to get a screen capture - but I don't have permissions. What I remember is you need to go into user accounts and groups and add the "SQL Server Agent Service Account" into the local Windows group for that service. See

    I believe the local Windows group is called SQLServer2005AgentUser.


  • I have an update...I tried running my package from the sql server through SSMS. When I do, it errors out at the first step where I execute my stored procedure that contains the sql to generate my XML data. Here's the error I get:

    Error Executing Query "EXEC operations.usp_getMustShipList" failed with the following error: "Required white space was missing." Possibel failure reasons: Problem with the Query: "Result set" property not set correctly, parameters not set correctly, or connection not established correctly.

    This is wierd since I can run the package from my machine through SSMS just fine with no errors.

    Any thoughts?


  • I figured it out. Nothing to do with permissions...I forgot that I changed my ADO.NET connection to a OLE DB connection. Once I changed it back to the ADO.NET, everything works great!


    thank you for the idea on how to send email from script object. because i use file attachments, above is the idea on how to do it.

    in my vb code:

    Dim varAttachment As Object

    varAttachment = New Attachment(Dts.Variables("gv_FileName").Value.ToString)


    so i added lines to dispose of the objects creating the file and sending mail in the script objects because i encountered an error - it was because i stopped the package midway to correct something. which reminded me of clean up even if we are using


  • just to wrap up my post above, although it is already a subtopic...

    due to corporate rules, file attachments may have to be password-protected. doing so in excel also automatically encrypts it (just let excel do it underneath).

    as of now, i reaally dont know if it was ClickOnce or the package ProtectionLevel, but i am just so glad (understatement) i finally got it running.

    see post below where i joined a discussion then polished off on how to send password-protected excel file as attachment

    Re: Password protect Excel output

  • Great idea!

    I just implemented my first HTML formatted email report and it works fine.

    At the end of the article Paul mentions the possibility combining multiple result sets in one mail. That's exactly what I need - but don't know how to do it.

    Can anybody give me a hint or a short example?

    Thanks in advance!

  • Hi Paul,

    I am trying to generate a package similar to what you have put it over here. I use the same variable , same name for all tasks too. But have one exception that- since i dont have adventure works DB, i have used my own query (i have put that below). But have problems in making it success.

    the "Get Daily Totals" (execute sql task) works perfectly , but i have problems in "Test output to HTML file" (XML Task). It fails with the error message :

    "[XML Task] Error: An error occurred with the following error message: "XSLT compile error.". "

    I dont know what is the cause for the error.


    The following is the code present in "SQL Query for Order Totals.sql"


    This script © 360Data 2008



    set@v_CurrentDate ='2008-08-22'

    if exists (SELECT 1 FROM











    PROC_DT = @v_CurrentDate




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


    elseselect cast(' ' as xml)


    The following is the code present in "Orders.xsl"

    body {margin-left: 20px; font-family: segoe ui, tahoma, sans-serif;}

    h1{color: #f00;}

    h2{size: 70%; color: #00f;}

    td{padding-right: 10px;}

    body {margin-left: 20px; font-family: tahoma, sans-serif;}

    No sales records were found for the specified date.


    Can u help in resolving this issue

  • Hi ,

    i am new to SSIS,

    i opened a SSIS package, but i didn't see any 'Connection Managers pane'.

    could you please let me know how to get it.

  • I took a different way out a while back - I have a job that just runs a series of emails - this one is hard coded going to me & my boss, but you get the idea. Others I select all possible emails from the DB and email the specific info to the users... (so where it is I have a variable in the sql) - been working pretty cool for a while. Easier for the non visual folks to understand 😉


    select Hdr.OrderNumber, hdr.shiptoname, hdr.createdate, hdr.requiredshipdate

    from Ordrhdr as hdr

    where hdr.shiptocountry = 'Canada' and hdr.orderfilled = 'O'

    if @@RowCount > 0


    SET @tableHTML =

    N' ' +

    N' ' +

    N' ' +

    N' ' +

    CAST ( ( SELECT td = Hdr.OrderNumber, '',

    td = Hdr.ShipToName, '',

    td = Cast(Hdr.CreateDate as CHar(12)), '',

    td = Cast(Hdr.RequiredShipDate as CHar(12)), ''

    from Ordrhdr as hdr

    where hdr.shiptocountry = 'Canada' and hdr.orderfilled = 'O'

    order by Hdr.RequiredShipDate

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N' ' ;

    EXEC msdb.dbo.sp_send_dbmail @recipients=';',

    @subject = 'New Canadian Order needs doc ',

    @body = @tableHTML,

    @body_format = 'HTML' ;


  • Great Article. Interesting approach. Thank you for the contribution.


  • UPDATE2: Doh! Just a typo in one of the variable names.

    Still, is there any way to put the script in a script task into debug mode when executing the package, so that you can step through the script with live data?

    UPDATE: I went back and deleted the script task and built a new one. This time, I kept all of the infrastructure code that SSIS inserts;

    I added the Imports for System.Net.Mail, and the script built correctly. However, when I run the package, I get the following error:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->

    Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from

    a collection on a container during execution of the package and the element is not there.

    I can't tell from the stack trace what the problem is, and I don't know how to invoke a debugger that will let me step through a script and see what the variable values are.

    Any suggestions?



    I tried to build the example using SQL Server 2008/BIDS 2008, and I could not get the Script to build. The error reported is:

    Error1Name 'Dts' is not declared.c1efb0bd34654788ba529d6d6268ff47\ScriptMain.vb2523st_4cf65c5d211547a78eb10be428868eb0

    There are 4 build errors - one for each reference to Dts.Variables. I have the Imports Microsoft.SqlServer.Dts.Runtime

    statement. Was there any change to the namespace/nomenclature in 2008? I suppose I could have a bad install - I haven't done much with SSIS since I installed SQL Server 2008.

    [font="Tahoma"]Eric Flamm, Flamm Consulting[/font]

  • Hi, Did anyone tried to send this type of report to a blackberry? I have mixed success with this, it works very well with a PDA but not so well with Berries.

    BI Guy

  • Excellent article. The way that I have been doing this is to create a report on SSRS and then send the report out in SSIS by calling the Subscription on SSRS. SSRS provides much more reporting functions and it is easy to format the report.

    Other than using SSIS for ETL, I also am using SSIS as a Emailer Services for the company. I have a Email_Queue table that has fields like to, cc, bcc, Subject, body, etc. The SSIS Emailer Services read the table every minute and send the email out and save the record into Email_Log or Email_Error_Log. For all the emails in each application, all they need to do is to insert data into the Email_Queue table and then the rest will be handled by SSIS.

    There is no doubt that SSIS can do much more other than just ETL.


  • Thanks very much for all the appreciative and useful comments. I'm afraid I don't have time at present to answer all the questions that have been asked - this article was submitted and first published almost a year ago when time was more plentiful. I'll try to get back to this at some stage but if anyone else can help in the meantime you won't hear me complaining!

    Several people have pointed out that SSIS isn't perhaps the most logical choice for mailing data. I completely agree, but the point of the article wasn't to build a mailing solution, but to show how flexible SSIS is and how you can put it to uses that extend far beyond simple ETL. Anyway, thanks again to everyone who responded.

Viewing 15 posts - 31 through 45 (of 85 total)

You must be logged in to reply to this topic. Login to reply