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
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