Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Sending SMTP mail from the Script Task

I've noticed that alot of people have a need to send SMTP email from a script task rather than the SendMail Task. A couple of the reasons for this are:

  • HTML emails
  • None-Windows authenticated SMTP servers

neither of which are supported by the SMTP Mail Task.

Well, its possible to use the script task as a route into using . Here's the basic code skeleton - its only really 4 lines of code. You can change it yourself to suit your requirements.

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(jamie.thomson@nospam.conchango.com, 
                        "jamie.thomson@nospam.conchango.com", "Subject", "body")
    mySmtpClient =
New SmtpClient("192.168.3.75")
    mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
    mySmtpClient.Send(myHtmlMessage)
    Dts.TaskResult = Dts.Results.Success
 
    Dts.TaskResult = Dts.Results.Success
  End Sub
End
Class
 

See. Dead easy!

 

-Jamie

 

Published Monday, July 03, 2006 6:40 PM by jamie.thomson

Comments

 

brainmeyers said:

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

March 15, 2007 9:50 PM
 

Su said:

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

March 29, 2007 1:53 PM
 

Subah said:

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.

March 30, 2007 6:22 PM
 

subah said:

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

Thanks.

April 2, 2007 2:11 PM
 

rajender19@yahoo.com said:

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

July 19, 2007 2:53 PM
 

ismailc said:

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

August 2, 2007 3:19 PM
 

AshishSinha said:

How can I add more recipients like Cc and Bcc?

December 6, 2007 1:38 PM
 

Tim Toennies said:

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

March 5, 2008 4:45 PM
 

GoldRam said:

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

March 21, 2008 7:05 PM
 

Keith said:

Thanks Jamie and GoldRam for all your contribution!

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

May 8, 2008 10:03 PM
 

Akber said:

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

May 12, 2008 4:57 PM
 

Englestone said:

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

-- Lee

May 20, 2008 12:40 PM
 

Englestone said:

June 25, 2008 3:45 PM
 

tv said:

Great!

Special thanks to GoldRam, it saves me some time.

July 9, 2008 2:33 PM
 

rubyA said:

Thanks Jamie.Nice Article.

July 18, 2008 6:59 AM
 

SN said:

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

July 22, 2008 1:43 PM
 

SN said:

Figured it out:

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

July 22, 2008 2:08 PM
 

HoggZilla said:

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

July 27, 2008 3:30 PM
 

Danny's .net World said:

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

July 31, 2008 7:43 PM
 

Colin said:

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

August 18, 2008 8:49 AM
 

jamie.thomson said:

Colin,

Do you get an error?

I don't knw much about SQL mail. Can it be manipulated using sprocs?

-Jamie

August 18, 2008 7:38 PM
 

Colin Macguire said:

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.

August 19, 2008 3:58 PM
 

deepak said:

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

August 23, 2008 7:36 AM
 

TWKnight said:

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/

November 14, 2008 8:25 PM
 

C?mo enviar una consulta SQL en formato HTML via e-mail? | hilpers said:

January 17, 2009 8:09 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems