A student in one of my recent SSIS classes asked if it's possible to send an email to a Gmail account, from inside SQL Server Integration Services.
The short answer is "yes", you can. Of course, the "how" is always in the details - and there are a few details.
First, we can't use the built-in SSIS email control flow task. Among other reasons, we need to specify a port number and we need to handle a few other properties for credentials that the send mail task doesn't expose.
So the answer is to write a script task that does the following:
- Creates an SMTP Client object (and objects for a Mail Message and an Attachment as well)
- Enables SSL
- Sets the SMTP client object credentials to the email credentials for the sender
This example uses a Gmail account for both the sender and recipient. If the sender were using something other than Gmail, then adjustments would need to be made to account for the sender's email.
Here's the code for the SSIS task, in C#. (Remember that the very top of the SSIS script would need to add references to the System.Net and System.Net.Mail namespaces).