Use SSIS to send emails by Joe Millay, 2014/10/09

Large-scale web sites typically send thousands of emails a day. Often, the code generating these emails is in separate applications resulting in non-standardized, difficult to maintain code. The solution in this article proposes to use the power of SQL Server and SSIS to send emails.

This solution is used by Community Health Network, Indianapolis, IN, http://www.ecommunity.com, which supports eight medical campuses, 70+ physician practices, an online retailer for durable medical equipment (http://www.homehealthmedical.com), and multiple outlier facilities. The site sends thousands of emails a day: patient reminders and confirmations, online retail shopping order confirmations, administrator notifications, etc. 

The SSIS package in this article has been in place for 5 years and, 3,000,000 emails later, it is still doing its magic. Instead of having mulitple lines of code in multiple locations, we now have a standardized email functionality.  The solution is comprised of three basic blocks :

• PART 1: A table where the email information is stored
• PART 2: An integration services package that sends the email
• PART 3: A SQL Server Agent job that runs the package

PART 1: The Table (tbl_SendEmail) and Its Insert Procedure (usp_ins_sendEmail) ...

read on here: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/71485/