Send authenticated SMTP email from SSIS

By Allen Stoner

ODBC Drivers for QuickBooks, Salesforce, SAP, MSCRM, SharePoint … Free Trial!

SSIS has a 'Send Mail Task', but it wont send via a SMTP server that requires authentication. By using the Script Task it is fairly simple to write some .NET code that will send an email via an authenticate STMP connection. Here is some sample VB.NET to send an email, it pulls all relavent information from variables that should be sent into the script from the SSIS package.

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

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()
        Dim Message As MailMessage = New MailMessage()
        Dim Smtp As New SmtpClient()
        Dim SmtpUser As New System.Net.NetworkCredential()

       Message.From = New MailAddress(Dts.Variables("sEmailSendFrom").Value, Dts.Variables("sEmailSendFromName").Value)

        Dim sTemp As String
        Dim aTemp As String()
        aTemp = Split(Dts.Variables("sEmailSentTo").Value, ";")
        For Each sTemp In aTemp
           Message.To.Add(New MailAddress(sTemp))
        Next
       Message.IsBodyHtml = False

       Message.Subject = Dts.Variables("sEmailSubject").Value
       Message.Body = Dts.Variables("sEmailBody").Value
        '-- Define Authenticated User
        SmtpUser.UserName = Dts.Variables("sEmailUser").Value
        SmtpUser.Password = Dts.Variables("sEmailPassword").Value

        '-- Send Message
       Smtp.UseDefaultCredentials = False
       Smtp.Credentials = SmtpUser
       Smtp.Host = Dts.Variables("sEmailServer").Value
       Smtp.Port = Dts.Variables("sEmailPort").Value
       Smtp.DeliveryMethod = SmtpDeliveryMethod.Network
        Try
           Smtp.Send(Message)
            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub
End Class

Related FAQs

Create an array of strings in SSIS

If you need to use an array of strings in SSIS it is a little difficult to create it as a variable in the package. It takes defining the package variable as a system.object and calling a script task, either VB or C# (example is VB.NET). In my example I set System:Package name as a ReadOnly variable and User:Categories as ReadWrite, this is also the one defined as the system.object. The sample Main procedure sets the first element of the string array to the package name.

Send authenticated SMTP email from SSIS (463 Views)