2

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.

"xxx@company.com,yyy@company.com"
xxx@company.com,yyy@company.com

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
user692942
  • 16,398
  • 7
  • 76
  • 175
Bryan Tercenio
  • 21
  • 1
  • 1
  • 3
  • 3
    https://stackoverflow.com/questions/23484503/how-to-send-email-to-multiple-recipients-with-mailmessage See here – Brad Apr 03 '19 at 19:07
  • Usually this happens when script task is unable to locate non-standard DLL in references. If you added such DLL - it can be your case; add this [DLL to GAC](https://stackoverflow.com/questions/6905640/registering-a-dll-in-gac). – Ferdipux Apr 04 '19 at 07:10

0 Answers0