Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

Sending scheduled SQL query results via HTML e-mail using SSIS Expand / Collapse
Author
Message
Posted Friday, September 3, 2010 6:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 22, 2011 9:41 AM
Points: 87, Visits: 280
Interesting article. I'm wondering why you chose SSIS instead of SSRS for this task. SSRS seems tailor-made for this.

Mike Hayes
Post #980155
Posted Friday, September 3, 2010 10:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 3:00 PM
Points: 12, Visits: 31
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
Post #980338
Posted Saturday, September 4, 2010 6:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #980713
Posted Sunday, September 5, 2010 2:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 9:23 AM
Points: 27, Visits: 52
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...:)
Cheers
/Allan
Post #980760
Posted Monday, September 6, 2010 3:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 19, 2013 3:11 AM
Points: 106, Visits: 33
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
Post #980981
Posted Monday, September 6, 2010 4:36 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 5:19 AM
Points: 646, Visits: 1,854
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 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.
Post #981009
Posted Monday, September 6, 2010 5:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:25 AM
Points: 43, Visits: 190
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.
Post #981035
Posted Monday, September 6, 2010 11:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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 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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems


  Post Attachments 
Protocol Output.gif (120 views, 5.53 KB)
Post #981202
Posted Monday, September 6, 2010 12:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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 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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #981222
Posted Tuesday, September 14, 2010 1:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 11:35 AM
Points: 311, Visits: 284
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

Post #985785
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse