SSIS: Sending mail using Script Task to a secure SMTP

Today a client reported that the SSIS had failed after they turned their SMTP server to a secure one. To my surprise i found out that there is no provision in the Send Mail Task to specify SMTP user name and password. I did lot of googling but did not find a complete solution any where. Anyways i found out every one was using Script Task for getting over the limitations of  SSIS send mail. So i ended up writting a small script that took just a few minutes and worked like a charm.

The following code shows how to specify SMPT authentication and also attach a file to the mail. You can do a lot many things you cant do with ‘send mail’; like changing the port number etc.

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

Public Class ScriptMain
	Public Sub Main()
		Dim fromAddress As MailAddress
		Dim toAddress As MailAddress
		Dim bccAddress As MailAddress

		Dim FilePath As String
		FilePath = Dts.Variables("ERRORBOX").Value.ToString()
		FilePath = FilePath + "\\rejected.dat"

		Dim EmailID As String
		EmailID = Dts.Variables("EmailID").Value.ToString()

		fromAddress = New MailAddress("abc@abcdefg.com", "Mr. Abc")
		toAddress = New MailAddress(EmailID)
		bccAddress = New MailAddress("efg@abcdefg.com", "Mr. Efg")

		Dim atach As Attachment
		atach = New Attachment(FilePath)

		Dim message As MailMessage
		message = New MailMessage(fromAddress, toAddress)
		message.Bcc.Add(bccAddress)
		message.Subject = "Test"
		message.Priority = MailPriority.High
		message.IsBodyHtml = False
		message.Body = "Attached is the file file you wish you had."
		message.Attachments.Add(atach)

		Dim smtpClient As SmtpClient
		smtpClient = New SmtpClient("mail.abcdefg.com", 25)
		smtpClient.Credentials = New System.Net.NetworkCredential("username", "password")
		smtpClient.Send(message)
		Dts.TaskResult = Dts.Results.Success
	End Sub
End Class
Advertisements

About Amit Singh

Software engineer; crazy about music, astronomy and radio controlled aircrafts. Big science/technology buff!
This entry was posted in Programming, SSIS and tagged , , , . Bookmark the permalink.

3 Responses to SSIS: Sending mail using Script Task to a secure SMTP

  1. kshal says:

    brother, u r IT programer izzit?

  2. Jordan says:

    Hi, i am newbie, can you show me step by step? How to setup in properties there?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s