October 8, 2014 at 10:16 pm
Comments posted to this topic are about the item Use SSIS to send emails
October 9, 2014 at 12:40 am
Nice article.
Does the email have some formatting? Does in include a signature from the Community Health Network for example?
(I noticed you have a column indicating HTML formatting)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2014 at 4:06 am
It's a questionable choice to use the System.Net.Mail namespace to send mail directly via SMTPClient rather than employing SQL Server's Database Mail.
This is, after all, an article that promotes the power of mail functionality on the database server. As such, why not make a call to sp_send_dbmail and thereby take advantage of SQL Server's built-in mail queue and its associated views?
Database Mail provides out of the box resilience against non-availability of your SMTP server through its queuing and the ability to specify multiple SMTP servers to serve the queue. It supports HTML formatted messages, attachments, and the ability to define queries that return results to be included in the message body. It's very powerful and should be considered before deciding to hand-craft a call directly to a single SMTP server via System.Net.Mail.
Rob
October 9, 2014 at 4:16 am
Rob CW (10/9/2014)
It's a questionable choice to use the System.Net.Mail namespace to send mail directly via SMTPClient rather than employing SQL Server's Database Mail.This is, after all, an article that promotes the power of mail functionality on the database server. As such, why not make a call to sp_send_dbmail and thereby take advantage of SQL Server's built-in mail queue and its associated views?
Database Mail provides out of the box resilience against non-availability of your SMTP server through its queuing and the ability to specify multiple SMTP servers to serve the queue. It supports HTML formatted messages, attachments, and the ability to define queries that return results to be included in the message body. It's very powerful and should be considered before deciding to hand-craft a call directly to a single SMTP server via System.Net.Mail.
Rob
If you use database mail, you even don't need SSIS and you can do everything in a single stored procedure.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 9, 2014 at 4:50 am
Koen Verbeeck (10/9/2014)
Rob CW (10/9/2014)
It's a questionable choice to use the System.Net.Mail namespace to send mail directly via SMTPClient rather than employing SQL Server's Database Mail.This is, after all, an article that promotes the power of mail functionality on the database server. As such, why not make a call to sp_send_dbmail and thereby take advantage of SQL Server's built-in mail queue and its associated views?
Database Mail provides out of the box resilience against non-availability of your SMTP server through its queuing and the ability to specify multiple SMTP servers to serve the queue. It supports HTML formatted messages, attachments, and the ability to define queries that return results to be included in the message body. It's very powerful and should be considered before deciding to hand-craft a call directly to a single SMTP server via System.Net.Mail.
Rob
If you use database mail, you even don't need SSIS and you can do everything in a single stored procedure.
That's what I've been doing personally. I'm still wet behind the ears, but I created a series of email stored procedures after my ETL process that are called and queued using DB mail.
I found the process easy to just use DB mail, especially when wrapping HTML and embedding variables that hold query results for informative email reports to the team.
I've also stayed away from including specific email recipients to my SP's with DB Mail. I only mail mailing list addresses to make management of those email SP's easier while also giving my team members a flexible way to subscribe and unsubscribe from those email notifications at will.
October 9, 2014 at 10:07 am
One benefit of using SSIS would be that you make sending the emails asynchronous and independent of application, no? I guess you could set-up a job just calling an SP that uses the built-in SQL Mail functionality to mimic the SSIS process.
In some environments SQL Mail might not be set-up/enabled/configured could be another reason to use SSIS.
Hope that this helps.
Thanks...Chris
October 9, 2014 at 10:15 am
CGSJohnson (10/9/2014)
I guess you could set-up a job just calling an SP that uses the built-in SQL Mail functionality to mimic the SSIS process.In some environments SQL Mail might not be set-up/enabled/configured could be another reason to use SSIS.
...making sure that if you're on SQL Server 2005 or later you use Database Mail rather than the now deprecated SQL Mail
October 9, 2014 at 12:35 pm
Thanks for the correction...yes, Database Mail. Thanks.
October 9, 2014 at 4:44 pm
Hi Koen, thanks for the compliment.
yes the emails do contain html/formatting. it depends on the app we are sending the email for.
the styles need to be inline, not a reference to an external style sheet. As an example, the html below is a sample order acknowledgment for our online retail store:
<html><head><style type='text/css' media='screen'>
body { FONT-SIZE: 8pt !important; LINE-HEIGHT: 12pt !important;
FONT-FAMILY: Verdana, Arial, Helvetica, sans-serif !important; color: #333333; } TABLE { FONT-SIZE: 8pt; }
TABLE TH { padding: 4px; background: #005179; color:
#febe10; font-weight: bold; } TABLE tr.row1 { color:#333; } TABLE tr.row2 { color:#333; background: #f1f1f1; }</style></head><body><img alt="Home Health Medical" src="http://www.homehealthmedical.com/images/HHM-logo.png" />
Hello ,
Your order is detailed below.
Medcheck Noblesville | 6
<b>Order Number:</b> 539
<b>Order Date:</b> 10/09/2014 06:35pm
<b>Order Type:</b> OCC
<b>Email: </b>jdoe@ecommunity.com
<b>Guarantor Info:</b>
<b>Patient Info:</b>
jane Doe
<table><tr><th>Item</th><th>Qty</th>
<th>Price</th></tr><tr class='row1'>
<td>WRIST w/THUMB LT S/M</td><td>1</td><td>$32.99</td></tr><tr><td><b>Subtotal</b></td><td></td><td>$32.99</td></tr><tr><td><b>Tax</b></td>
<td></td><td>$0.00</td></tr><tr><td>
<b>Total</b></td><td></td><td>$32.99</td></tr></table>
</table>
</body></html>
October 9, 2014 at 4:55 pm
Good points, Rob--we did consider Database Mail and for the reasons Chris specified we decided to go with the SSIS option. The biggest factor in our decision was the configuration/availability of database mail--our DBA's just didn't want to do it at that time.
I'm sure you know--the decision was "multi-layered"
Thanks for the feedback.
October 14, 2014 at 7:19 am
I've used a similar approach going back 10 years or so. It's worked well, and it's abstracted from changes to the mail system in SQL. I ran multiple jobs, in some cases on multiple servers, to deliver different classes of mail (password resets ahead of bulk mail). I also supported a limited number of tokens for bulk email jobs. Notification Services was the "answer" to this problem briefly, but was overly complex and didn't survive the next release, would have been nice to see it evolve.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply