I am using a send mail task in my ssis package. Is there a way to get a delivery notification. Thanks.

you can use SCRIPT TASK instead of send mail task, and write c# or vb.net code to get delivery notification, this is a C# sample of that code: http://shareourideas.com/2010/08/07/how-to-send-mail-with-delivery-notification-in-dotnet/http://www.rad.pasfu.com

I don't think so. Priority is about the only 'advanced' option you have available in the stock task. But here's the thing, unless you are using YOUR account for the FROM, then the delivery notification would go back to the service account inbox, not yours.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I used the code you sent me and adjusted it for vb.net and it worked fine. The problem I am having now is I am building a dynamic "To" list of mail recipients and the string is longer than 256 characters. Is there a way to create a distribtion list through code and add the recipients to the list? Or add recipients to an already created distribtion list? Thanks.

If need be my account will be the sender. It wasn't originally but I will make it if thats what it takes to get the notification.

I used the code you sent me and adjusted it for vb.net and it worked fine. The problem I am having now is I am building a dynamic "To" list of mail recipients and the string is longer than 256 characters. Is there a way to create a distribtion list through code and add the recipients to the list? Or add recipients to an already created distribtion list? Thanks. you can create To list dynamically by SSIS VARIABLES, with a STRING type variable you haven't limit for 256 character, just create a variable, and set its value, and pass it as ReadOnlyVariables in SCRIPT task. does it make sense to you?http://www.rad.pasfu.com

This is the way I am currently doing it. I am looping through an ADO Recordset object, each time concatinating the email address to a String variable, I am using a script task to build my string. The string variable is an expression in my Send Mail Task. The string variable is built correctly, no truncation. However it is truncated in the address line of the actual email.

any thought on this? Thanks.

did you tried my suggestion with SCRIPT Task? you can read data from variable to define receiver of mails with ReadOnlyVariables.http://www.rad.pasfu.com

Yes. I was able to email using a script and get a failure notification. Then I tried substituting a user string variable for the recipients. Here is my script: Dim smtp = New SmtpClient smtp.Host = "relay1.xxxxx.com" smtp.UseDefaultCredentials = True Dim msg = New MailMessage("sender@xxxxx.com", Dts.Variables("email_to_line").Value) msg.Subject = "Testing Email using code" msg.Body = "Testing email using code." msg.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure msg.Priority = MailPriority.Normal smtp.Send(msg) The Dts Variable "email_to_line" contains all the email addresses to send the email to Thanks

do you have more than one email address in email_to_line variable? if yes, you should split it and send mail message in a loop one by one, try this: (Note that I assumed COMMA is separator for to_line emails, you can change it if you want) Dim ToLineArray As String() = Dts.Variables("email_to_line").Value.ToString().Split(Convert.ToChar(",")) For Each ToAddress As String In ToLineArray Dim smtp = New SmtpClient smtp.Host = "relay1.xxxxx.com" smtp.UseDefaultCredentials = True Dim msg = New MailMessage("sender@xxxxx.com", ToAddress) msg.Subject = "Testing Email using code" msg.Body = "Testing email using code." msg.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure msg.Priority = MailPriority.Normal smtp.Send(msg) Next http://www.rad.pasfu.com

We don't want to send separate emails. We want each recipient to know who else recieved the email. The email_to_line has about 20 recipients, each about 25 characters. The last 9 or so recipients gets truncated in the email to line. I seem to be getting conflicting answers to wether or not their is a 256 character limit and if using a variable can get around this. My other thought is to add the 20 users to a dynamic distribution list and put the name of the distribution list in the email line. Do you think this is doable? Using a script, build a distribution list, add the member to the list and use the list name in the to address? Thanks.

as this thread shows you can use your string COMMA Delimited value exactly in TO section, and there is not 256 character limitation. could you put a break point at script task lines and let us know what is your variable value? is it correct ?http://www.rad.pasfu.com

I generally log all the messages which i want to send to the users in a table in SQL Server and populate the table in html and give the hyperlink to the html page in a mail and send to the user. in this way the user can see the latest info every day , if he wants to if he deletes the alert by mistake , he can still see the status in the regular hyperlink where he has to visit. a dba can track what are all the messages he sent to a particular user over a period of time and everything will be under control.thanks, chandra sekhar pathivada www.calsql.com | SQL Server Community Website

I am using a SEMI COMMA in my string. I repeated my name for a test and the string variable is correct, here it is: jnapolit@sovereignbank.com;jnapolit@sovereignbank.com;jnapolit@sovereignbank.com;jnapolit@sovereignbank.com;jnapolit@sovereignbank.com;jnapolit@sovereignbank.com;jnapolit@sovereignbank.com;jnapolit@sovereignbank.com;jnapolit@sovereignbank.com;jnapolit@sovereignbank.com This variable @[User::email_to_line] is in the Expression ToLine This is the actual To: line in the email, note how the last one gets truncated each name is 26 characters long plus ; times 9 names = 27*9 = 243 plus the 12 characters for jnapolit@sov = 255 Napolitano, James; Napolitano, James; Napolitano, James; Napolitano, James; Napolitano, James; Napolitano, James; Napolitano, James; Napolitano, James; Napolitano, James; jnapolit@sov Thanks

any thought on my latest reply, thanks

Hi jimnsov, What is the mail server you used? Based on my research, the Microsoft Exchange Server does not have the limitation of maximun number of recepient. As a workaround, you can try to create a temporary distribution group via the following article: http://msdn.microsoft.com/en-us/library/ms180903(v=vs.80).aspx Thanks, Jin ChenJin Chen - MSFT