Send SMTP mail from Script task (Send to Multiple Recipents ???)

Question

Send SMTP mail from Script task (Send to Multiple Recipents ???)

mickielsmith

Right there with Babe

Points: 723
More actions
March 30, 2009 at 1:46 pm

#130160

Send SMTP mail from Script task
Hello Forumers,
My objective I'm trying to resolve is to send either a success or failure email to multiple recipents using SMTP mail from Script Task in SSIS. I pretty much know this script code below will achieve for sending 1:1 (1 to 1 recipicent), but my objective is 1:m (1 to many recipicents). I know it's something simple, can anyone guide me here? Greatly appreciate any feedback, thanks.
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net
Public Class ScriptMain
Public Sub Main()
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim smtpserver As String = "192.168.9.78"
Dim Subject As String = "subject"
Dim Body As String = "body"
myHtmlMessage=New MailMessage(firstname.lastname@somecompany.com,
"firstname.lastname@somecompany.com", Subject, body)
mySmtpClient = New SmtpClient(smtpserver)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 1

I am not sure of sending to a list 'all at once', but can help with sending one at a time in a loop using ForEach and create a custom function for it which takes in a list and parsing the list (ordinal list) to send to a list of recipients.Let me know.

Mukti SSCrazy Points: 2826 More actions · Answer 2

You can add multiple address to 'To', 'CC' or 'BCC' by using following code:

myHtmlMessage.To.Add("test1@test.com")

myHtmlMessage.To.Add("test2@test.com")")

myHtmlMessage.CC.Add("test3@test.com")")

myHtmlMessage.CC.Add("test4@test.com")")

myHtmlMessage.Bcc.Add("test5@test.com")")

myHtmlMessage.Bcc.Add("test6@test.com")")

Useful link: http://www.systemnetmail.com/faq/3.2.3.aspx

You may write code to populate these values from your DB too.

HTH

~Mukti

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 3

Although very easy (the earlier post), i would recommend, u know to get your email list values from a database table, flat file etc, so that it saves u the excess coding and build one common function to implement it.

mickielsmith Right there with Babe Points: 723 More actions · Answer 4

Forumers,

Thank you for all your very fast replies. I have found a workable solution. I can just add a comma between recipients in my toaddress string and that seems to work just fine.

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

mickielsmith (3/30/2009)
Forumers,
Thank you for all your very fast replies. I have found a workable solution. I can just add a comma between recipients in my toaddress string and that seems to work just fine.

Keep in mind ToAddress property is limited to 256 characters. So with not that many email recipients, you will hit the limit pretty soon. A better approach would be what Vishal proposed, using a ForEach container to iterate over a list and send email to each recipient.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/