Protean

As the SSIS send mail component doesn’t have an option to send html formatted messages, I thought it would help to post some code that implements the system.web.mail namespace.

Considering that sending messages is quite a regular task for any organisation, it might even be worth creating this as a standalone package and calling it each time you need to send a mail. Or even better still, writing a custom component instead.

If you haven’t already you will probably need to add a reference in your script editor to “system.web.dll”

Having done this the following code should allow you to send an email in html format. Obviously to make this dynamic, you would want populate the message, recipient, from and subject variables with your SSIS variables.

Imports System
Imports System.Web.Mail
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()
Dim message, subject, from, recipient As String
Dim mailobject As SmtpMail
Dim mailmessage As New MailMessage

message = "This is a test message”
subject = “Test”
from = “test@test.com”
recipient = “me@test.com”

Try
With mailmessage
.BodyFormat = MailFormat.Html
.Body = message
.From = from
.To = recipient
End With
Catch ex As Exception
Events.FireError(1, “Problem creating message: “, ex.Message, “”, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
Try
With mailobject
.SmtpServer = “mail.test.com”
.Send(mailmessage)
End With
Catch ex As Exception
Events.FireError(1, “Problem sending message: “, ex.Message, “”, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

Dts.TaskResult = Dts.Results.Success
End Sub

End Class


This is the simpler (in my view - not much of a .net coder) .Net 1.0 framework namespace for sending email. There is obviously a newer version (there always is just when you get the hang of something!) , the system.net.mail namespace which you can use if you’re on the .net 2.0 framework or above.

Here is a working package that takes the usual email parameters to send an html formatted email this time using the newer system.net.mail namespace.

Happy days

Frank

This entry was posted on Friday, April 4th, 2008 at 1:46 pm and is filed under Nuggets, SSIS, SQL Server 2005, Scripting. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.