Sending scheduled SQL query results via HTML e-mail using SSIS
-
Thanks a lot, this article is very helpful. But one problem I ran into was that the XML generated from my query exceeds the 4000 character limit on string variables in SSIS. I was able to dump the XML to a file, and can create the HTML file just fine from it, but now I'm not exactly sure what the best approach is for emailing an HTML file as the body text of the email? Does anyone have a sample if this? I assume I have to modify the SSIS email script to read the text file and assign it to the mail body, but I'm not sure of the syntax for all of this...
As a reference for others that are trying to do what I'm doing, one way to dump the XML to a file is described here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336&whichpage=2
But to save time, the format is simply:
use pubs
execute master..xp_cmdshell 'bcp "select top 10 * from ( select 1 root ) as root right outer join pubs..authors on 1 = 1 for xml auto, elements" queryout c:\bcp.xml -T -c -r -t'
(this gets the root tag in there for those of us still using sql server 2000, but if you are using sql server 2005+ you can use the "root" parameter directly in your query as is used in this article)
Also you may be wondering if there is some tool out there that will automatically generate XSL (xml style sheets) files for you based on your query. I could not find much out there in googling this, but did found this guys code as a good starting point:
http://www.access-programmers.co.uk/forums/showthread.php?t=113050
I ended up creating my own quick and dirty VB6 app to simply take a sql query and generate the XSL for me with the click of a button. If others want my code/app let me know and I'll find a place on the net to upload it.
-
Gordo et al,
Another option is to not generate the html file - just generate the xml file, which itself contains a reference to the xsl stylesheet. You can then include a link to it in your email. (I know that's not exactly what you were asking for.)
e.g. prefix your xml document with
to reference a stylesheet in the same directory as the xml files.
When the xml file is opened with internet explorer (can't speak for other browsers), IE will do the transform for you, and display the xml as html using the said stylesheet.
This is useful for avoiding sending potentially large emails around, and also offloads the transformation work to IE.
Regards,
David.
-
very well explained example.
user:varSalesSummaryHTML (or whatever the variable where HTML has to be stored) has a limit of 4000 chars, is there a workaround if my query returns little more than 4000 characters?
-
Dump to file instead of storing in SSIS variable. This is described in my post above.
-
If we have knowledge superior data just about http://www.primewritings.com/price, you must receive the http://www.primewritings.com essay writing example just about it
-
Interesting article. I'm wondering why you chose SSIS instead of SSRS for this task. SSRS seems tailor-made for this.
Mike Hayes
-
This is helpful for me. I never knew how to convert the results of a query into XML. I am working on a project right now that this might be relevant towards.
___________________________________
Brian A. Zive
Assistant Director, Systems
Business Intelligence Analyst
Massachusetts General Hospital
Development Office -
First, yeah... I know this is a repost of an older article. The author did a nice job on the article. I've got no complaints there. Well done.
What I do have a complaint about is how bloody difficult it's become to send a well formatted email. It used to be you simply ran a query through sp_MakewebTask and then send the file. It took about 2 steps... not 13.
You can also do this quite easily in T-SQL and never have to save a file. And did you see the size of the style sheet that was posted on this thread? There's just no need for this complexity and there's no need to even go near SSIS, SSRS, or anything but a little T-SQL and sp_send_dbmail. The method in this article needed a query, a script, an SSIS package and none of it was simple. The addition of a style sheet in the discussion made it only that much more complicated.
Maybe an article on the subject of simplicity is in order. 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi!
As new to SSIS, I tried this. It worked fine to produce the html-presentations when doing this manually, but not in SSIS.(sql2008)
I did this ex on own data, and many problems came up.
How can I get SSIS to produce 'encoding="ISO8859-1" ' or like för special letters?
Bad characters Errors because SSIS produces "utf-8"
The XLS file only produces half of what was produced manually (drag/drop into explorer). I got the headline, or I got the rows without headlines / details.
I even tried to make a table as shown in the link (http://www.xmlfiles.com/xsl/), but compiler error in SSIS. I was not able to find out how to check this. Are there limits for XLS files in SSIS. This ex also works fine manually.
Next point was emailing.
I did not succed how to use the script för email. I copied the whole script inte SSIS, but no. Error messages in scripts are not for new beginners..
It would be nice to see the complete solution for how you did it?
It works with emailing using attachment, but the produced html file was no good...:):w00t:
Cheers
/Allan
-
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports System.Net.Mail
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As Object
Dim varAddresses As String
varMailBody = Dts.Variables("varSalesSummaryHTML").Value
varAddresses = Dts.Variables("varMailTo").Value.ToString
varHTMLMail = New MailMessage("noreply@domain.com", varAddresses, "Daily Order Summary", varMailBody)
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient("Your_SMTP_Server_Name")
varSMTPClient.UseDefaultCredentials = True
varSMTPClient.Send(varHTMLMail)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
above code giving error
-
Jeff Moden (9/4/2010)
And did you see the size of the style sheet that was posted on this thread?....The addition of a style sheet in the discussion made it only that much more complicated.....Maybe an article on the subject of simplicity is in order. 😛Hi Jeff,
As for the size of the stylesheet, it's roughly the same size as the html document it produces minus the data - which is as it should be.
I think that the use of xml and xsl is at the absolute centre of what the article was about (and not an addition to the discussion.)
If there are corners to be cut, I'd suggest (and I can hear you groan as I write this) a 2 line CLR function which takes an xsl, xml and returns the transformed document. That enables to you to do all this without troubling SSIS.
I have to say that one of my top pet hates is seeing html constructed with T-sql.
I note, Jeff, that you have a preference - and it is your right - for doing everything that can possibly be done with SQL with SQL. The result is that you can do some pretty crazy things with SQL with leave me for one breathless if not speechless. But I would argue that for generating html especially sophisticated html is either the job of an ASP application or why not take advantage of the xml functionalities in SQL and transform the result with XSL, as Paul has done. (Horses for courses?)
I include a link to articles [/url]I've written in a similar vein - which I fear you may hate! But just so you know precisely where I'm coming from!
Best regards,
David McKinney.
-
Thanks for all the responses to the article, it's great to have so many people obviously take the time and trouble to read it properly and respond both here and via mail.
I'm mildly horrified at the notion that anyone might think that I was suggesting HTML-via-mail-via-SSIS as the best or only way to serve this functional problem. My starting point was quite different, much more like, "look, you can do this with SSIS too". Not just simple data pumping from A to B, say. The XML/HTML demo was a means to this end, not a recommendation.
I'm quite in agreement with those who've suggested that there are better tools for and simpler ways of achieving the same ends, and am absolutely in favour of choosing the simplest approach to any particular requirement. You know the saying that goes something like "when all you have is a hammer, everything becomes a nail"? Well, this article was started with the thought that SSIS would be the only tool I'd use, and all else followed from there.
I'm very grateful for all the responses. I'm afraid that detailed technical answers to the various questions that have been posted aren't practical right now; the article is around two years old at this stage, written and submitted at a point when I had more spare time.
-
David McKinney (9/6/2010)
Jeff Moden (9/4/2010)
And did you see the size of the style sheet that was posted on this thread?....The addition of a style sheet in the discussion made it only that much more complicated.....Maybe an article on the subject of simplicity is in order. 😛Hi Jeff,
As for the size of the stylesheet, it's roughly the same size as the html document it produces minus the data - which is as it should be.
I think that the use of xml and xsl is at the absolute centre of what the article was about (and not an addition to the discussion.)
If there are corners to be cut, I'd suggest (and I can hear you groan as I write this) a 2 line CLR function which takes an xsl, xml and returns the transformed document. That enables to you to do all this without troubling SSIS.
I have to say that one of my top pet hates is seeing html constructed with T-sql.
I note, Jeff, that you have a preference - and it is your right - for doing everything that can possibly be done with SQL with SQL. The result is that you can do some pretty crazy things with SQL with leave me for one breathless if not speechless. But I would argue that for generating html especially sophisticated html is either the job of an ASP application or why not take advantage of the xml functionalities in SQL and transform the result with XSL, as Paul has done. (Horses for courses?)
I include a link to articles [/url]I've written in a similar vein - which I fear you may hate! But just so you know precisely where I'm coming from!
Best regards,
David McKinney.
Well said and understood. Please keep in mind that I'm not trying to bad mouth anyone or any technique. I just don't see the need for the complexity of it all even if you don't do it through SSIS. You don't need to keep track of code for a CLR nor instantiate it. You don't need to keep track of a style sheet nor worry about if the DBA or OPs group will actually let me have access to it. You don't need to generate XML and then have a script convert it to HTML nor do you need to keep track of a script. There are a lot of things you don't need to worry about if you can do it all in T-SQL because all of the code is all in one place. Oddly enough, with the advent of FOR XML PATH, you can easily create the necessary HTML in T-SQL and the whole thing is shorter than most style sheets. Here's an example I created for another post. Please know that the only thing I wasn't able to test in this sproc was the actual call to sp_send_dbmail.
CREATE PROCEDURE dbo.SendProtocol/**********************************************************************************************************************
Purpose:
Send an email for all open Protocols for a give Study Manager ID.
(This IS a part of the solution)
**********************************************************************************************************************/
--===== Declare I/O Parameters
@pStudyManagerID INT
AS
--===== Environmental Presets
SET NOCOUNT ON;
--===== Declare local variables
DECLARE @Body NVARCHAR(MAX),
@Email NVARCHAR(50),
@FirstName NVARCHAR(15)
;
--===== Get the email address and name for the given study manager id
SELECT @FirstName = FirstName,
@EMail = Email
FROM #Staff
WHERE StaffID = @pStudyManagerID
;
--===== Create the body of the message including a nicely formatted table of the protocols
SELECT @Body =
------- Create the table and the table header. (Easily readable HTML)
'
<html>
<body>
<p>Dear ' + @FirstName + ',</p>
<p>The following table contains a list of your open Protocols.</p>
<table border="1" cellspacing="0" style="text-align:center">
<caption>Protocols</caption>
<tr style="background-color:AliceBlue"><th>Protocol No</th><th>Open to Enrollment Date</th><th>Enrollment Goal</th></tr>
'
------- 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 = ProtocolNo, N'', --<td></td> = "data" element in a row
td = CONVERT(NCHAR(11), OpenToEnrollDate, 106), N'',
td = CAST(EnrollmentGoal AS NVARCHAR(10)), N''
FROM #Protocol
WHERE IsOpen = 1
AND StudyManagerID = @pStudyManagerID
FOR XML PATH('tr'),TYPE --<tr></tr> = row encapsulation
)AS NVARCHAR(MAX))
,'</tr>','</tr>'+CHAR(10)+SPACE(8))
------- Finalize the HTML
+ '
</table>
</body>
</html>'
;
--===== All set. Send the email.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifier_Profile',
@recipients = @EMail,
@subject = 'Your open Protocols',
@body = @Body
;
GO
I realize that you good folks can't test that code because you don't have the data, etc, etc. With that in mind, here's what one run of the code looks like in HTML...
<html><body>
<p>Dear Donald,</p>
<p>The following table contains a list of your open Protocols.</p>
<table border="1" cellspacing="0" style="text-align:center">
<caption>Protocols</caption>
<tr style="background-color:AliceBlue"><th>Protocol No</th><th>Open to Enrollment Date</th><th>Enrollment Goal</th></tr>
<tr><td>BRE150</td><td>26 Aug 2010</td><td>250</td></tr>
<tr><td>LUN120</td><td>26 Aug 2010</td><td>50</td></tr>
</table>
</body>
</html>
Here's what the email body looks like for a given ID when received...
As a bit of a side bar, people keep telling me the ol' "Jeff, to a hammer, everything is a nail" and they proceed to lecture on the subject of why I shouldn't be using T-SQL for "everything". I suggest turning this around a bit. Learn how to identify what a nail actually is and then understand that you don't need a thousand pound air compressor, air hose, electricity, extension cords, 32 types of nail guns, and a truck to get it all there when it's just a brad that you're trying to hammer in. 😛 😉 Heh... to a brad, everything is a hammer. :hehe:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Paul Clancy (9/6/2010)
I'm mildly horrified at the notion that anyone might think that I was suggesting HTML-via-mail-via-SSIS as the best or only way to serve this functional problem. My starting point was quite different, much more like, "look, you can do this with SSIS too". Not just simple data pumping from A to B, say. The XML/HTML demo was a means to this end, not a recommendation.Heh... please pardon those of us who were mildly horrified 😛 at the notion that it looked that way especially since the following was in the article...
... so I went about figuring out if it was possible to automatically notify users that these records had not been deleted via e-mail. They needed to get full details in a user-friendly format so they could take further action, so HTML mail was the desired means of delivery.
The package I came up with to achieve this can be used to mail any suitable SQL result set and [font="Arial Black"]proves once again the flexibility of SSIS and the different ways it can be used to solve a variety of problems beyond its everyday use as an ETL tool[/font].
It's probably just me, but that looks like a recommendation for doing this in SSIS. 😉
Anyway, please take no offense. Whether I agree with the complexity of the method or not, the concept does work and it's a very well written article with graphics and examples in all the right spots. Well done, Paul. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
There is a simpler alternative for using Script Task for emailing - just use Execute SQL Task with the following code
EXEC msdb.dbo.sp_send_dbmail @recipients='name1@example.com;name2@example.com',
@subject = @Subj,
@body = @Message,
@body_format = 'HTML' ;
Sure - Database Mail must be enabled and configured in advance for instance which used for sp_send_dbmail call
Regards
Viewing 15 posts - 46 through 60 (of 84 total)
You must be logged in to reply to this topic. Login to reply