How can I fix "Error: Exception has been thrown by the target of an invocation." Error in SSIS Script task sending to multiple emails

Bryan Tercenio picture Bryan Tercenio · Apr 3, 2019 · Viewed 9.3k times · Source

I'm setting up email alert using SSIS script task. The code works when CC field is only one email but gets an

"Error: Exception has been thrown by the target of an invocation."

for multiple emails.

This pulls data from a view in SQL database. I tried the code with just a single email and it worked but gets an error for multiple emails in CC field. I tried adding "" on the CC column but still don't work.

"[email protected],[email protected]"
[email protected],[email protected]

This is what I have in SSIS

SSIS Package

variables

Public Sub Main()

    Dim htmlMessageFrom As String =
Dts.Variables("From").Value.ToString
    Dim htmlMessageTo As String =
Dts.Variables("To").Value.ToString
    Dim htmlMessageCc As String =
Dts.Variables("CC").Value.ToString
    Dim htmlMessageSubject As String =
Dts.Variables("Subject").Value.ToString
    Dim htmlMessageBody As String =
Dts.Variables("Body").Value.ToString
    Dim smtpConnectionString As String =
DirectCast(Dts.Connections("SMTP Connection 
Manager").AcquireConnection(Dts.Transaction), String)
    Dim smtpServer As String =
smtpConnectionString.Split(New Char() {"="c, ";"c})(1)

    SendMailMessage(
  htmlMessageFrom, htmlMessageTo, htmlMessageCc,
  htmlMessageSubject, htmlMessageBody,
  True, smtpServer)

    Dts.TaskResult = ScriptResults.Success

End Sub

Private Sub SendMailMessage(
ByVal From As String, ByVal SendTo As String,
ByVal SendCc As String,
ByVal Subject As String, ByVal Body As String,
ByVal IsBodyHtml As Boolean, ByVal Server As String)

    Dim htmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient

    htmlMessage = New MailMessage(
From, SendTo, Subject, Body)
    htmlMessage.IsBodyHtml = IsBodyHtml


    htmlMessage.CC.Add(SendCc)


    mySmtpClient = New SmtpClient(Server)
    mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
    mySmtpClient.Send(htmlMessage)

End Sub

Answer