Tuesday, 6 August 2013

Sending mail within SSIS - Part 2: Script Task

Case
I want to send mail within SSIS, preferably HTML formatted. What are the options?

Solutions
There are a couple of solutions to mail within SSIS:
  1. Send Mail Task
  2. Script Task with SmtpClient Class
  3. Execute SQL Task with sp_send_dbmail
  4. Custom Tasks like Send HTML Mail Task or COZYROC

To demonstrate the various solutions, I'm working with these four SSIS string variables. They contain the subject, body, from- and to address. Add these variables to your package and give them a suitable value. You could also use parameters instead if you're using 2012 project deployment.
Add these four variables to your package










B) Script Task
The Script Task is a little harder to use than the Send Mail Task (if you don't have .Net knowledge), but it doesn't have the same drawbacks as the Send Mail Task. It uses the SmtpClient Class and it has an HTML formatted mail option and a configurable port number.

1) SMTP Connection Manager
This first step is optional. I like to use as much as possible standard connection managers. Right click in the Connection Managers window and add an SMTP Connect manager. Add the SMTP server and change other options if necessary (other options are not used in this example). The alternative is to use an extra SSIS string variable for storing the SMTP Server.
SMTP Connection manager
















2) Add Script Task
Add a Script Task to your Control Flow (or one of the event handlers). Give it a suitable name and add the SSIS variables as readonly variables to the Script Task.
Add all SSIS variables as ReadOnly





















3) The Script
In the Scipt I'm using the variables and the connection manager to fill the properties of the SMTP client. Copy the contents of the Main method to your method and add the extra import/using on top.
// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;          // Added

namespace ST_df6618207373422d961b80ca8b6a56e2
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            // Storing SSIS variables in .Net variables. You could skip this step and call the SSIS variables in the actual mail code
            // to reduce the number of code lines. Or you could fill these .Net variables with hardcoded values.
            String SendMailFrom = Dts.Variables["SendMailFrom"].Value.ToString();
            String SendMailTo = Dts.Variables["SendMailTo"].Value.ToString();
            String SendMailSubject = Dts.Variables["SendMailSubject"].Value.ToString();
            String SendMailBody = Dts.Variables["SendMailBody"].Value.ToString();

            // Get SMTP Server from SMTP Connection Manager. Alternative is to use extra variables or paramters instead:
            // String SmtpServer = Dts.Variables["SmtpServer"].Value.ToString();
            String SmtpServer = Dts.Connections["My SMTP Connection Manager"].Properties["SmtpServer"].GetValue(Dts.Connections["My SMTP Connection Manager"]).ToString();

            // Create an email and change the format to HTML
            MailMessage myHtmlFormattedMail = new MailMessage(SendMailFrom, SendMailTo, SendMailSubject, SendMailBody);
            myHtmlFormattedMail.IsBodyHtml = true;

            // Create a SMTP client to send the email
            SmtpClient mySmtpClient = new SmtpClient(SmtpServer);
            mySmtpClient.Port = 2525; // If you want to use a different portnumber instead of the default. Else remove this line.
            mySmtpClient.Send(myHtmlFormattedMail);

            // Close Script Task with success
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

or use VB.Net code

' VB.Net Code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail             ' Added

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()
        ' Storing SSIS variables in .Net variables. You could skip this step and call the SSIS variables in the actual mail code
        ' to reduce the number of code lines. Or you could fill these .Net variables with hardcoded values.
        Dim SendMailFrom As String = Dts.Variables("SendMailFrom").Value.ToString()
        Dim SendMailTo As String = Dts.Variables("SendMailTo").Value.ToString()
        Dim SendMailSubject As String = Dts.Variables("SendMailSubject").Value.ToString()
        Dim SendMailBody As String = Dts.Variables("SendMailBody").Value.ToString()

        ' Get SMTP Server from SMTP Connection Manager. Alternative is to use extra variables or paramters instead:
        ' Dim SmtpServer as String = Dts.Variables("SmtpServer").Value.ToString();
        Dim SmtpServer As String = Dts.Connections("My SMTP Connection Manager").Properties("SmtpServer").GetValue(Dts.Connections("My SMTP Connection Manager")).ToString()

        ' Create an email and change the format to HTML
        Dim myHtmlFormattedMail As New MailMessage(SendMailFrom, SendMailTo, SendMailSubject, SendMailBody)
        myHtmlFormattedMail.IsBodyHtml = True

        ' Create a SMTP client to send the email
        Dim mySmtpClient As New SmtpClient(SmtpServer)
        mySmtpClient.Port = 2525 ' If you want to use a different portnumber instead of the default. Else remove this line.
        mySmtpClient.Send(myHtmlFormattedMail)

        ' Close Script Task with success

        Dts.TaskResult = ScriptResults.Success
    End Sub

#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.

    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

#End Region

End Class

4) The result
Close the Script Task and execute it: An email message with html formatting!
HTML formatted email





















If you're not into .Net, but really like TSQL then check out the next solution: Execute SQL Task with sp_send_dbmail

19 comments:

  1. Hi if we want send message to multiple mails that time how to modify this code can any body help me .and i tried between two mails i used ; symbol but its not working .
    String SendMailTo = Dts.Variables["SendMailTo"].Value.ToString();

    ReplyDelete
  2. Would it kill you to mention what version your developing this with. Seriously not everybody is up to 2012 and for those that aren't it might save them time in not bothering with the tutorial and looking for another solution with their earlier version.

    ReplyDelete
    Replies
    1. Hi 'Unknown',

      Would it kill you to mention your name when you complain? If you read the instructions carefully you see that you must copy the content of my main method to your main method. Then it will just work fine in SSIS 2008 or even 2005. Hopefully you can now create your own blog to help all those that won’t understand mine.

      Delete
    2. Joost this article is very helpful! Ignore the haters. Thanks again!

      Delete
    3. Dear Joost,

      Re managing the above keyboard warrior: 10 out of 10
      Also the "how to" was very informative also

      Cheers
      Tom

      Delete
    4. Dude... what a D bag... this a good article. Thanks for taking time out of you life to help me.

      Charaka

      Delete
  3. Thanks Joost

    This worked like a charm, and saved me a lot of time and effort.
    And for those who really want to know, I am using SQL Server 2008 R2. ;)

    Regards,
    Bhawesh

    ReplyDelete
  4. Hello,

    I am working on a project that requires a Month (direct input in the package) to be passed into the Mail Subject line...I am struggling to make this happen as I have some knowledge of VB but I am new to SSIS.
    I have used the script option to actually send the mail message (I needed html functionality), and that works perfectly! Now, I just need a variable value (which does already exist) to be passed in, for example "This is a Line", where subject is a replaced variable value.

    Can you help?

    Thank you! Your post has been extremely helpful!

    ReplyDelete
  5. Thank you!

    But is there a way to pass a dynamic variable into a subject line of a Mail Task?

    I am using the vbscript option, so I assume I could do a simple replace...thus far, nothing is working!

    The email sends, but for formatting purposes, I need that dynamic variable passed into the subject line.

    Thanks,

    Bree

    ReplyDelete
    Replies
    1. No dynamic is not possible. You always have to hardcode the variable name in the script. So either set all necessary variables as readonly variables and come up with an if/case-construction in the .net code where you choose the right variable...
      Or create an expression on a variable that fills it with the right value and use that variable in your Script Task.

      Delete
  6. Okay, thank you - I am finding that it is not possible as well. That subject line is fairly static as set by SSIS/Script.

    As for the dynamic variable I need to include somewhere in the email, I suppose I will opt to try including it in the body of the message.

    Thank you so much! I have bookmarked your blog site!

    -Bree

    ReplyDelete
  7. It's hard to believe that no one had already submitted a Connect Feature Suggestion for this, so I just did. Please upvote https://connect.microsoft.com/SQLServer/feedback/details/1437047/ssis-send-mail-task-needs-additional-parameters.

    ReplyDelete
  8. Great Article Thank you so much!

    ReplyDelete
  9. I m getting error like Error: Exception has been thrown by the target of an invocation.please help me on this

    ReplyDelete
    Replies
    1. It's a very generic error without any clues, but it's probably a typo in a variable name (or the scope of the variable). Otherwise you need to debug your script task to which line is causing the error.

      Delete
  10. What about the DeliveryMethod and Credentials ? Have you missed them ?

    ReplyDelete
    Replies
    1. You can extend this basic script to your own needs. The example uses the credentials of the user running the job. Just search for something like 'smtpClient c#'

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...