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/

Solving Kerberos Issues in SSRS When Running Beside IIS By Ian Massi 2014/10/07

http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/116001/

Introduction

When relying on Windows authentication to give your users access to SQL Server resources from Reporting Services, Active Directory will use either NTLM or Kerberos to resolve access rights.  If Reporting Services isn’t running on the same server as the resources you’re trying to access, then Kerberos is your only option to allow Windows credentials to be passed through.  If Kerberos isn’t pleased with your configuration, it will visit upon you the dreaded double-hop authentication issue.  You can resolve this issue by configuring Active Directory to appease it.  However, when Reporting Services is running on a server that’s also running IIS (Internet Information Services), things can become a bit trickier.  In this harsh environment, appeasement of Kerberos is not enough for us.  We will tame it.

Environment

In our environment we were running SQL Server Reporting Services 2005 (SSRS 2005) on a separate server that already had IIS installed.  SSRS 2005 requires IIS to be able to handle the web side of things and we didn’t want IIS on our SQL Server.  We also have SQL Server Analysis Services (SSAS) in our environment.  There are reports that use SSAS as a data source, which always requires Windows authentication.  So we had the web site for SSRS set up to run in IIS under an application pool that used a domain account while the Windows Service Identity for SSRS ran under NT Authority\NetworkService.

read on ... (http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/116001/)