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

  • hi there,

    i am also having problem generating the html. It seems to be working just fine but html data column are empty. for example there are 10 records, it is putting 10 empty rows in there instead of data filled.

  • Hey Jeff, I like your simplified T-SQL only solution a lot, I just used it for a small project. Here are a couple small tips for others trying to use the code Jeff posted, you need to specify body_format='HTML' for the send mail call, and you also need to add blanks to the end of all data that could be blank, so that grid lines are rendered in all versions of outlook/internet explorer (it will work in some versions without them). The thing is, you can't use a regular " " space, and you can't use " ", you have to use the alternate space generated by holding down the alt key, typing 255, then releasing the alt key. It looks the same as a regular space but is treated differently by outlook/internet explorer. The column width issues can drive you crazy because people running different machines with different versions of windows/outlook/internet explorer could potentially all see different formatting of the HTML table. If there are good solutions to this issues, please post them. I used OP's SSIS solution a while ago in another project and I can't remember now if that solution suffered from the same formatting problem?? Maybe that is the value added in the (albeit very complicated) SSIS version.

    At any rate, here is my working version of the t-sql only solution previously posted (obviously you would have to specify your own table name and query to test:


    @Email NVARCHAR(300),


    -- @FirstName NVARCHAR(15) --If you want to use the recipients name in the email


    --===== You could get the email address and name from a table or hard code it below

    --can use multiple email addresses separated with a ";"

    select @EMail = ''

    select @cc = ''

    --SELECT @FirstName = 'x',

    -- FROM #MyEmailTable

    -- WHERE ID = @SomeParameter


    --===== Create the body of the message including a formatted HTML table of query results data

    SELECT @Body =

    ------- Create the table and the table header. (Easily readable HTML)

    --You could use a greeting here: <p>Dear ' + @FirstName + ',</p>

    --Note: Your table column headings are defined below, you can add spaces ( ) to make the columns wider, but this will only work on certain versions of internet explorer / outlook, some renderers will ignore the spaces.




    <p>The following table shows blah blah blah....</p>

    <table border="1" cellspacing="0" style="text-align:center">

    <caption>Coverage Policy Exceptions</caption>

    <tr style="background-color:AliceBlue"><th>Exception Order</th><th> Exception_Name  </th><th>Plan_Name</th><th>Internal_Policy_Nm</th><th>Policy_Nm </th><th>  Policy_Nbr  </th><th>  Status  </th><th>  Drug_Nm  </th><th>  Indication  </th><th>  SubIndication  </th><th>  Prev_Internal_Policy_Nm  </th><th>  Prev_Policy_Nm  </th><th>  Prev_Policy_Nbr  </th><th>  Prev_Status  </th><th> Change_Date_Time </th><th>  Changed_By_ID  </th><th>  Changed_By_Name  </th>



    ------- Create the rest of the table. Filled in from data in the table.

    + SPACE(8)

    + REPLACE(--This just indents each row to make rows in the HTML apparent and easy to read

    CAST((SELECT td = Exception_Order, N'', --<td></td> = "data" element in a row

    --You might want to format dates, or convert things to text as in the examples below:

    --td = CONVERT(NCHAR(11), OpenToEnrollDate, 106), N'',

    --td = CAST(XXX AS NVARCHAR(10)), N''

    --You need to add something to the end of any column that could be blank if you want grid lines to appear in all versions of windows/internet explorer/outlook (some versions work fine, others remove grid lines around blank cells)

    --Please note, this is NOT a regular space - hold down alt key, type 255 then release alt key, this creates something like a space. A regular space does NOT resolve the grid line problem for SOME versions of windows/internet explorer/outlook.

    td = Exception_Name + ' ', N'',

    td = [Plan_Name] + ' ', N'',

    td = [Internal_Policy_Nm] + ' ', N'',

    td = [Policy_Nm] + ' ', N'',

    td = [Policy_Nbr] + ' ', N'',

    td = [Status] + ' ', N'',

    td = [Drug_Nm] + ' ', N'',

    td = [Indication] + ' ', N'',

    td = [SubIndication] + ' ', N'',

    td = [Prev_Internal_Policy_Nm] + ' ', N'',

    td = [Prev_Policy_Nm] + ' ', N'',

    td = [Prev_Policy_Nbr] + ' ', N'',

    td = [Prev_Status] + ' ', N'',

    --A date with time can be formated as below

    td = replace(convert(varchar(8), [Change_Dt], 10), '-', '/') + ' ' + substring(convert(varchar(20), [Change_Dt], 9), 13, 5) + ' ' + substring(convert(varchar(30), [Change_Dt], 9), 25, 2) + ' ', N'',

    td = cast([Change_By] as varchar(6)) + ' ', N'',

    td = [Change_By_Name] + ' ', N''

    FROM SomeTableName --Add where clause and/or order by clause as needed

    FOR XML PATH('tr'),TYPE --<tr></tr> = row encapsulation



    ------- Finalize the HTML

    + '





    -- All set. Send the email. (google sp_send_dbmail if you need help setting it up)

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SpecifyYourProfileNameHere',

    @recipients = @EMail,

    @copy_recipients = @cc,

    @subject = 'Specify Email Subject here',

    @body = @Body,

    @body_format = 'HTML' --If you don't include this, you will see all HTML tags in the email and it will not render correctly



  • Quoting the last sentense of the article:

    "Deploy the package on your SSIS server, schedule it as desired and you're done."

    How can we schedule the email so that it can be sent, let's say at 3.00 AM every day?

    Also, how can we pass parameters, so that the query is executed with the date just before sending the emails.



  • Great article. Thank you very much.

    Could you please tell me how I can add a chart in the report so that I can send a chart besides the table

    Thanks in advance,


  • marco.yandun (10/29/2011)

    Could you please tell me how I can add a chart in the report so that I can send a chart besides the table

    Just click the 'display as chart' option, which is right next to the 'make me a cappucino' check box. 😎

  • Hey David,

    Could you please clarify where I can find that option?



  • Sorry Marco...I was being sarcastic. Generating a chart in HTML is way outside of the scope of this article / discussion.

    As a general guideline, I would advise that you make sure that you understand code before you use it - or else you're going to find yourself out of your depth.

    Have you thought about reporting services perhaps, as a sensible way to get charts from SQL Server?

  • Yes David,

    I thought of it. Using RS is an easy way to create charts. My problem is I need to create the report and send it by email automatically.

    I already followed the steps as explained in this excellent article and managed to send the tabular report automatically using SQL Server Agent, but now the new requirement to send a chart besides the tabular report has been asked.

    Any advice from you will be highly appreciated,


  • Marco,

    You can setup a subscription in SSRS by clicking the "Subscriptions" tab. You can configure it to send an email.

  • Thank you very much Geoffrey for your advice,

    Got another different problem now: The Object Datasources just disappear from my Visual Studio 2008 when opening a report. Do you know how can I get it back? last time I had to format my machine and got it for some months until yesterday that I had the same problem. Now I just have formatted it again, but this time, on my second time I edited my project, it just vanished again... this is making me crazy...



  • Is it the "Report Data" window you're missing? It shows you the Built-in Fields, Parameters, Images, Data Sources and Datasets? If so, try Ctrl + Alt + D. Or View > Report Data (at the bottom).

  • That's just it Geoffrey!!

    Thank you very much for your help. Your advice just saved me a lot of time and headaches 😀


  • Nice article it got me going with SSIS and E-mail

    I have a question is it possible to send Multiple Recipent. If we add recipent e-mail address in the variable "varMailTo" and How??

    I Tried with; but the package failed 1 email works fine.

    Thanks again


  • Yes you can, try with the following code:

    Dim varHTMLMail As MailMessage

    Dim varSMTPClient As SmtpClient

    Dim varMailBody As Object

    Dim varAddresses As String

    varMailBody = Dts.Variables("varProductionSummaryHTML").Value

    varAddresses = Dts.Variables("varMailTo").Value.ToString

    varHTMLMail = New MailMessage()

    varHTMLMail.From = New MailAddress("")

    varHTMLMail.Subject = "xxxxxxx"



    varHTMLMail.Body = varMailBody

    varHTMLMail.IsBodyHtml = True

    varSMTPClient = New SmtpClient("") 'Your_SMTP_Server_Name

    varSMTPClient.UseDefaultCredentials = True


  • Thanks marco.yandun

    That will work for me.

Viewing 15 posts - 61 through 75 (of 84 total)

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