Share this post

When you send an email with the Mail Task in a SSIS, you cannot specify the port for the connection.

The solution is to use a script task and set the specific port. Here is an example to send an email in HTML and Text/plain with an attachment. I've used variables for the recipient, cc, subject, port, ...

    Try

            Dim oViewPlain As AlternateView
            Dim oViewHtml As AlternateView
            Dim oMessage As New MailMessage()

            oMessage.From = New MailAddress(Dts.Variables("User::varFromMailFile").Value.ToString())

            'Separator for mail adresses
            Dim sSep As String()
            ReDim sSep(0)
            sSep(0) = ";"

            'Subject
            oMessage.Subject = Dts.Variables("User::varSubjectMailFileAssogestione").Value.ToString()

            'Recipients
            If Not Dts.Variables("User::varToMailFile").Value.ToString().Equals("") Then
                Dim strTO As String = Dts.Variables("User::varToMailFile").Value.ToString()
                Dim oTO As String() = strTO.Split(sSep, StringSplitOptions.RemoveEmptyEntries)
                For Each oTO_MailAdress As String In oTO
                    oMessage.To.Add(oTO_MailAdress)
                Next
            End If

            'CC
            If Not Dts.Variables("User::varCCMailFile").Value.ToString().Equals("") Then
                Dim strCC As String = Dts.Variables("User::varCCMailFile").Value.ToString()
                Dim oCC As String() = strCC.Split(sSep, StringSplitOptions.RemoveEmptyEntries)
                For Each oCC_MailAdress As String In oCC
                    oMessage.CC.Add(oCC_MailAdress)
                Next
            End If

            'BCc
            If Not Dts.Variables("User::varBCCMailFile").Value.ToString().Equals("") Then
                Dim strBCC As String = Dts.Variables("User::varBCCMailFile").Value.ToString()
                Dim oBCC As String() = strBCC.Split(sSep, StringSplitOptions.RemoveEmptyEntries)
                For Each oBCC_MailAdress As String In oBCC
                    oMessage.Bcc.Add(oBCC_MailAdress)
                Next
            End If
			
            'Create an alternate view for your mail
            'Message type plain/text
            oViewPlain = AlternateView.CreateAlternateViewFromString("Hello," _
                            & Chr(10) & Chr(10) & "Here is the file " & strFile & "." _
                            & Chr(10) & Chr(10) & "------------------------------------------------------------------------" _
                            & Chr(10) & Chr(10) & "Yours sincerely." _
                            & Chr(10) & Chr(10) _
                            , Nothing, "text/plain")
			'Message in HTML
            oViewHtml = AlternateView.CreateAlternateViewFromString("" _
                            & "<p style=""font-family:Arial;font-size:12px;"">" _
                            & "Hello," _
                            & "<br /><br />Here is the file " & strFile & "." _
                            & "<br /><br />Yours sincerely.." _
                            & "</p><DIV>&nbsp;</DIV><BR>" _
                            , Nothing, "text/html")

            'Add the views to the message
            oMessage.AlternateViews.Add(oViewPlain)
            oMessage.AlternateViews.Add(oViewHtml)

            'Message priority : Hight = 2, Loaw = 1, Normal = 0
            oMessage.Priority = MailPriority.Normal
            oMessage.IsBodyHtml = True

            'SMTP Server
            Dim smtpServer As String = Dts.Connections("SMTP Connection Manager").ConnectionString.Replace("SmtpServer=", "").Substring(0, Dts.Connections("SMTP Connection Manager").ConnectionString.Replace("SmtpServer=", "").IndexOf(";"))
            Dim oSmtpMail As New SmtpClient(smtpServer)
			
			'Exchange Port
            oSmtpMail.Port = CInt(Dts.Variables("User::varExchangePort").Value.ToString())

			'Attachment
            oMessage.Attachments.Add(New Attachment(strFilePath))
            oSmtpMail.Send(oMessage)

			'Succes Result
            Dts.TaskResult = Dts.Results.Success

        Catch ex As Exception
            ' post the error message we got back.  This is the old error catch code that might work better with SSIS.
            Dts.Events.FireError(0, "Script Task Error - SendMail", ex.Message.ToString(), String.Empty, 0)
			
			'Failure result
            Dts.TaskResult = Dts.Results.Failure
        End Try
    End Sub