blogs.conchango.com

Jamie,

Your comments were very helpful.  Thanks!!!  FYI, I added the following line before the .send so that it would format my email in HTML.  Otherwise it was sending it as plain text.

myHtmlMessage.IsBodyHtml = True

Brian

Relay this article solved my big problem while using SSIS, I need to send email when job failed or compled succesfully but I started using SQL Server email it is given lot of problem, but this article helped me lot so I can use script in SSIS also.

Great article ... nice article...

Hello,

I was having trouble replicating the above code to send emails. For some reason the code did not parse due to the first parameter within the =MailMessage() in

myHtmlMessage=New MailMessage(jamie.thomson@nospam.conchango.com,"<email>", "Subject", "body")

It does not like the '.' in the from email address and expects to see a ',' instead. Please forgive my ignorance on the subject and do let me know if anyone else faced similar issues.

Thanks in advance.

Subah.

I was able to get this working by including the "From" within quotes and by making sure the server supported the services.

Thanks.

In my SSIS package, I am looking for a file, if file is not found, I want send a message, where would I set it up.

Raj

Hi,

I've added the above code but unable to succed.

Firstly i don't know where to download the dll to add in the reference as i don't have them

Microsoft.SqlServer.Dts.Runtime  all i have Microsoft.SqlServer.DtsRuntimewrap

System.Net.Mail

System.Net

Secondly my code does not work, added html - what am i doing wrong?

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

  myHtmlMessage=New MailMessage(ismailc@parmalat.co.za, Daily Sales, <p class=MsoNormal><font size=2 color=navy face=Verdana><span style='font-size:10.0pt;font-family:Verdana;color:navy'>Hi, </span></font></p><p class=MsoNormal><font size=2 color=navy face=Verdana><span style='font-size:10.0pt;font-family:Verdana;color:navy'>Attached find the Daily Sales Report.</span></font></p><p class=MsoNormal><font size=2 color=blue face=Verdana><span style='font-size:10.0pt;font-family:Verdana;color:navy'>Regards&nbsp;</span></font></p><p class=MsoNormal><t  size=2 color=navy face=Verdana><span style='font-size:10.0pt;font-family:Verdana;color:navy'><img width=100 height=51 src="\\Srv08-za063\source\sitelogo.gif"></span></font></p>)

       mySmtpClient = New SmtpClient("10.18.2.54")

       mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials

       myHtmlMessage.IsBodyHtml = True

       mySmtpClient.Send(myHtmlMessage)

       Dts.TaskResult = Dts.Results.Success

       Dts.TaskResult = Dts.Results.Success

   End Sub

End Class

How can I add more recipients like Cc and Bcc?

Additionally it may be desirable to change the priority on the email (notably for an OnError event handler).  Add the following one line to the script:

       myHtmlMessage.Priority = Mail.MailPriority.High

Awesome blog, Jamie. I can't tell you how many times I've used the great information here. Thought some of your visitors might need to authenticate using external authentication credentials (e.g. via SMTP relay on their ISP's network). Here's my trivial contribution to your great SSIS blog:

mySmtpClient.Credentials = New System.Net.NetworkCredential("<username>", "<password>")

Thanks Jamie and GoldRam for all your contribution!

I'm finally able to send email from with SSIS package!

I am following this but getting the blank email. I have followed the articla of Paul Clancy for the Sending schedule SQL Query results via HTMIL.

http://www.sqlservercentral.com/articles/SSIS/62678

This is also necessary if you want to explicitly set the SMTP Port!

-- Lee

http://englestone.blogspot.com/2008/05/ssis-send-mail-task-change-smtp-port.html

Great!

Special thanks to GoldRam, it saves me some time.

Thanks Jamie.Nice Article.

Does anyone know how to add attachments into the mail using the above script as a basis?

Figured it out:

myHtmlMessage.Attachments.Add(New Attachment("c:\example.txt"))

Very good, just what I was looking for. Thanks.

How to attached Multiple files with send mail task or using script component.

Hi Jamie,

My SMTP mail task works 100% when I execute if from within BIDS, however as soon as I import the package into the SSIS Store within SQL server the mail task no longer works.

Do you know if there is a specific reason for this? Also, is it possible to set up the Mail Task to use the SQL Mail profile that has already been set up in SQL Server 2005?

Thanks in Advance,

Colin

Hi Jamie,

Sorry for the late reply, been an absolutely hectic two days. The only error message I got was when I looked at the Event Viewer and saw that the package had failed. Other than that nothing was said about the Mail Task completing at all.

With regards to the stored proc thing - Not too sure. I've searched all over Google but no one has mentioned sending mail via the Database Mail from within SSIS. I've also looked through the WROX Professional SSIS and two other books I have and no one mentions it.

I've logged a post on MSDN with the hope that someone can help me.

Have a good day,

Colin.

can we use this Credentials without using the password and username.

actually it's not a good method rgt..pls help me.

mySmtpClient.Credentials = New System.Net.NetworkCredential("<username>", "<password>")

I just found out that any attached files will stay locked until the MailMessage object is disposed, which doesn't happen automatically at the conclusion of the Script task. To force proper disposal, implement the MailMessage object in a "Using" block:

-------

Using myMessage As New MailMessage(fromAddr, toAddr)

           myMessage.Subject = subject

           myMessage.Body = bodyText

           myMessage.Attachments.Add(New Attachment(FilePath))

           myMessage.Priority = Mail.MailPriority.High

           mySmtpClient = New SmtpClient("mail.something.com")

           mySmtpClient.Credentials = New System.Net.NetworkCredential("user", "password")

           mySmtpClient.Send(myMessage)

End Using

--------------

This will allow you to delete or move the attachment immediately following script execution.

Originally discussed here: http://social.msdn.microsoft.com/forums/en-US/netfxnetcom/thread/44e4f16e-cf2e-4446-b404-d9e82daac556/