SSIS toolbox provides Send Mail Task for sending emails from integration service. It allows only sending text mail with SMTP Connection Manager.
SMTP Connection Manager supports only anonymous and windows authentication. If you use mail server outside of the organization boundary or need to send html format mail this task is useless.
Here’s the screenshots to describe this limitation.
|Send Mail Task|
|SMTP Connection Manager|
The alternative way, we could use Script Task to send mail directly or use Execute Process Task to trigger another process to send mails.
1. Send Mail using Script Task
a) First we create a few variables to pass mail information to the task.
Note that some ISPs block port 25, in this case you should use alternative port instead.
b) Pass variables to the task.
c) Scripts to be executed is as following. The namespace System.Net.Mail is needed in order to send mail.
By specifying the executable location, and optionally pass in some arguments, the task will trigger the other process to send mails. This especially useful in Service-Oriented Architecture.
Conclusion, there are many approaches for sending any kind of mails from integration service (SSIS) other than Send Mail Task.