Monday, July 26, 2010

Send Mail Task in SSIS 2008

How to send a notification/mail to specific group of peoples through SSIS packages? Thanks to Send Mail Task. This can be achieved easily using send mail task.
By using the Send Mail task, a package can send messages if tasks in the package workflow succeed or fail, or send messages in response to an event that the package raises at run time. For example, the task can notify a database administrator about the success or failure of the Backup Database task.

You can configure the Send Mail task in the following ways:
  • Write the message text for the e-mail.
  • Write a subject line for the e-mail message.
  • Set the priority level of the message. The task supports three priority levels: normal, low, and high.
  • Specify the recipients on the To, Cc, and Bcc lines. If the task specifies multiple recipients, they are separated by semicolons.
I'll explain this by an example:

Create a new SSIS Package and rename it with SendMailTask.dtsx.

Add two package variable MessageSource and SMTPServer of type string to this package as shown below:
Add a new SMTP Connection manager and configure its properties. Enter SMTP Connection manager in Name and Connection for sending mails in Description boxes. Now enter your SMTPServerName in SMTP server box as shown below:

Set SmtpServer property of above connection manager with variable User::SMTPServer by clicking on Expression in properties of SMTP Connection Manager. This is highlighted in below snap shot:

Drag and drop Script Task to set your message. Double click on Script Task to open Script Task Editor, Select User::MessageSource as ReadWriteVariables and click on Edit Script... button to set the message. Write below code in main function. (This is for VB.NET, you can choose C# or VB.NET)

Public Sub Main()
   'Set MessageSource variable
   Dts.Variables("User::MessageSource").Value = "Hi," & vbCrLf & _
   "This is a test mail to check Send Mail Task in SSIS!" & vbCrLf & vbCrLf & _
   "Thanks and Regards," & vbCrLf & "_____________" & vbCrLf & "Hari"
   Dts.TaskResult = ScriptResults.Success
End Sub
However, the message text can be a string that you provide, a connection to a file that contains the text, or the name of a variable (in our case) that contains the text.
Drag and drop Send Mail Task and double click on it to open Send Mail Task Editor. Select Mail from Left side and set mail properties as shown below:
Thats all. We are done with Send Mail Task setup, this package will look like below snapshot. Just right click on package in Solution Explorer and Execute. Receiver will get an email as soon as package executes successfully. However, mail delivery depends on the response of SMTP Server as well.
  1. The To, Cc, and Bcc lines are limited to 256 characters.
  2. The SMTP connection manager supports only anonymous authentication and Windows Authentication.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.