Today a client reported that the SSIS had failed after they turned their SMTP server to a secure one. To my surprise i found out that there is no provision in the Send Mail Task to specify SMTP user name and password. I did lot of googling but did not find a complete solution any where. Anyways i found out every one was using Script Task for getting over the limitations of SSIS send mail. So i ended up writting a small script that took just a few minutes and worked like a charm.
The following code shows how to specify SMPT authentication and also attach a file to the mail. You can do a lot many things you cant do with ‘send mail’; like changing the port number etc.
Imports System Imports System.Data Imports System.Math Imports System.Net Imports System.Net.Mail Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Dim fromAddress As MailAddress Dim toAddress As MailAddress Dim bccAddress As MailAddress Dim FilePath As String FilePath = Dts.Variables("ERRORBOX").Value.ToString() FilePath = FilePath + "\\rejected.dat" Dim EmailID As String EmailID = Dts.Variables("EmailID").Value.ToString() fromAddress = New MailAddress("email@example.com", "Mr. Abc") toAddress = New MailAddress(EmailID) bccAddress = New MailAddress("firstname.lastname@example.org", "Mr. Efg") Dim atach As Attachment atach = New Attachment(FilePath) Dim message As MailMessage message = New MailMessage(fromAddress, toAddress) message.Bcc.Add(bccAddress) message.Subject = "Test" message.Priority = MailPriority.High message.IsBodyHtml = False message.Body = "Attached is the file file you wish you had." message.Attachments.Add(atach) Dim smtpClient As SmtpClient smtpClient = New SmtpClient("mail.abcdefg.com", 25) smtpClient.Credentials = New System.Net.NetworkCredential("username", "password") smtpClient.Send(message) Dts.TaskResult = Dts.Results.Success End Sub End Class