.

Summary

Sending email via SMTP in SSIS, including a sample of sending email from a Script Task using .NET.

The default method for sending email within SSIS is the Send Mail Task. Now and then people want more control in which case the Script Task can be used, and the .Net System..Net.Mail namespace which has full support for SMTP mail.

Sample VB.Net from the SSIS Script Tasks:

1 - A simple script example to send email.

 Imports System
 Imports System.Data
 Imports System.Math
 Imports Microsoft.SqlServer.Dts.Runtime
 Imports System.Net.Mail 


 Public Class ScriptMain


         Public Sub Main()


                 Dim mailServer As String = "mail.domain.com"
                 Dim fromAddress As String = "system@domain.com"
                 Dim toAddress As String = "darren.green@domain.com"
                 Dim subject As String = "New Message"
                 Dim body As String = "Hello, my email message."


                 Dim smtp As New SmtpClient(mailServer)
                 Dim message As New MailMessage(fromAddress, toAddress, subject, body)


                 smtp.Send(message)


                 Dts.TaskResult = Dts.Results.Success


                 End Sub


 End Class

2 - A simple script that sends email with a file attachment. If the attachment does not exist, then the email is not sent, the method is exited.

 Imports System
 Imports System.Data
 Imports System.Math
 Imports Microsoft.SqlServer.Dts.Runtime
 Imports System.Net.Mail 


 Public Class ScriptMain


         Public Sub Main()


                 Dim mailServer As String = "mail.domain.com"
                 Dim fromAddress As String = "system@domain.com"
                 Dim toAddress As String = "darren.green@domain.com"
                 Dim subject As String = "New System Files"
                 Dim body As String = "Please find attached your new files."
                 Dim fileName As String = "C:\Folder\File.txt"


                 If Not File.Exists(fileName) Then Return


                 Dim smtp As New SmtpClient(mailServer)
                 Dim message As New MailMessage(fromAddress, toAddress, subject, body)
                 message.Attachments.Add(New Attachment(fileName))


                 smtp.Send(message)


                 Dts.TaskResult = Dts.Results.Success


                 End Sub


 End Class

It would of course be sensible to use variables or a connection to manage the details like the server and the to and from addresses.