I need to pull a set of data about users THROUGH a stored procedure and send an email to each of them on certain days. Using SSIS, what tasks I can use to do this? I CAN NOT USE Send Mail Task as I don't have access to a local SMTP server.
Send Mail Task can talk to external/third-party mail servers. SMTP Connection Manager's SMTP server setting can be set to any valid Internet/intranet host name (external or internal). However, there's a catch: the SMTP Connection Manager's only authentication option is for Windows authentication. Most external SMTP servers require username/password authentication.
http://stackoverflow.com/a/25654022/117424 talks about several ways to bypass this limitation.