Use SSIS to send emails

  • jmetape

    Mr or Mrs. 500

    Points: 516

    Comments posted to this topic are about the item Use SSIS to send emails

  • Koen Verbeeck

    SSC Guru

    Points: 258907

    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

  • Rob CW

    SSC Enthusiast

    Points: 163

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258907

    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

  • xsevensinzx

    One Orange Chip

    Points: 25531

    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.

  • CGSJohnson

    SSCertifiable

    Points: 6954

    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

  • Rob CW

    SSC Enthusiast

    Points: 163

    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

  • CGSJohnson

    SSCertifiable

    Points: 6954

    Thanks for the correction...yes, Database Mail. Thanks.

  • jmetape

    Mr or Mrs. 500

    Points: 516

    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>

  • jmetape

    Mr or Mrs. 500

    Points: 516

    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.

  • Andy Warren

    SSC Guru

    Points: 119675

    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 11 (of 11 total)

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