Summary of Send Mail Task in SQL Server SSIS and Azure Data Factory

The article discusses the Send Mail Task in SQL Server’s SSIS (SQL Server Integration Services) and its functionality in Azure Data Factory. The Send Mail Task allows a package to send email notifications based on the success or failure of tasks in the package workflow or in response to an event triggered at runtime.

You Know it’s Time to Manage Those Passwords 😉

  Key Configurations:

• Message Text: The content of the email.
• Subject Line: Subject of the email.
• Priority Level: Three levels – normal, low, and high.
• Recipients: To, Cc, Bcc lines (limited to 256 characters each).
• Attachments: Files can be attached, separated by the pipe (|) character.
• SMTP Connection Manager: Used for sending the email (only supports anonymous and Windows Authentication).

  Message Source Options:

Can be a string, a file, or a variable name.

  Custom Logging:

• SendMailTaskBegin: Indicates start of the task.
• SendMailTaskEnd: Indicates completion of the task.
• SendMailTaskInfo: Provides descriptive information.

  Important Notes:

• If an attachment file doesn’t exist, an error will occur.
• SMTP supports only anonymous and Windows Authentication.

  UI Options:

Properties can be set through the SSIS Designer or programmatically.

The article also provides information about setting these properties and gives references to related technical articles and topics.

Get an Email Server HERE!

 Send Mail Task in SSIS Example

  SSIS Designer:

1. Drag and Drop: From the SSIS Toolbox, drag and drop the Send Mail Task into your Control Flow.
2. Configure: Double-click the task to open the “Send Mail Task Editor”.
   • SMTPConnection: Select an existing SMTP connection or create a new one.
   • From: Provide the sender’s email address.
   • To: Enter recipient email addresses separated by semicolons.
   • Subject: Enter the subject of the email.
   • MessageSourceType: Choose between Direct Input, File Connection, and Variable.
3. Error Handling: You can use “Precedence Constraints” to link your Send Mail Task to other tasks based on success, failure, or completion.
4. Logging: You can enable logging on the Send Mail Task by right-clicking on the SSIS package design surface and selecting “Logging…”.

  Send Mail Task with C# Code

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.SendMailTask;

class Program
{
    static void Main(string[] args)
    {
        Package pkg = new Package();

        // Add a Send Mail Task
        Executable exe = pkg.Executables.Add("STOCK:SendMailTask");
        TaskHost th = exe as TaskHost;
        SendMailTask smt = th.InnerObject as SendMailTask;

        // Configure the task
        smt.SmtpConnection = "yourSmtpConnectionManager";
        smt.FromLine = "sender@example.com";
        smt.ToLine = "recipient@example.com";
        smt.Subject = "Task Status";
        smt.MailMessageSourceType = SendMailMessageSourceType.DirectInput;
        smt.MailMessageSource = "The task was successful.";

        // Error handling
        // (You would generally use Events in SSIS for error handling e.g. OnError event)

        // Execute the package
        DTSExecResult result = pkg.Execute();

        // Logging (Custom logging can be done post package execution)
        if (result == DTSExecResult.Failure)
        {
            foreach (DtsError local_DtsError in pkg.Errors)
            {
                Console.WriteLine("Error: {0}", local_DtsError.Description);
            }
        }
    }
}