locked
Sending More than one recepients in To and Cc using Script Task RRS feed

  • Question

  • Hi,

    I able to send mail with HTML body format using script task, but i am not able to send more than one recipients with the below code. Kindly provide solution to achieve this task with the below code.

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */

    using System;
    using System.IO;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Net.Mail;
    using System.Net;

    namespace ST_b28025465e064c28a92afa59da1b0075.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {

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

            /*
            The execution engine calls this method when the task executes.
            To access the object model, use the Dts property. Connections, variables, events,
            and logging features are available as members of the Dts property as shown in the following examples.

            To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
            To post a log entry, call Dts.Log("This is my log text", 999, null);
            To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

            To use the connections collection use something like the following:
            ConnectionManager cm = Dts.Connections.Add("OLEDB");
            cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

            Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
            
            To open Help, press F1.
        */

            public void Main()
            {
                // TODO: Add your code here
                string errorMsg = Dts.Variables["User::msg"].Value.ToString();

                string EmailMsgBody = String.Format("<HTML><BODY><P>{0}<br></BODY></HTML>",
                errorMsg);

                bool ishtml = true;

                string from = Dts.Variables["User::from"].Value.ToString();
                //string to = "gurumoorthym@frost.com;gopalb@frost.com";
                
                string to = Dts.Variables["User::to"].Value.ToString();
                //Mail.To.Add(to);
                string subject = Dts.Variables["User::subject"].Value.ToString();

                string server = "servername";//Dts.Variables[?ServerAddress?].Value.ToString();

                SendMailMessage(to, from, subject, EmailMsgBody, ishtml, server);
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            private void SendMailMessage(string SendTo, string From, string Subject, string Body, bool IsBodyHtml, string Server)
            {

                MailMessage htmlMessage;
               
                SmtpClient mySmtpClient;
            
                htmlMessage = new MailMessage(SendTo,From, Subject, Body);

                htmlMessage.IsBodyHtml = true;

                mySmtpClient = new SmtpClient(Server);


                mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;

                mySmtpClient.Send(htmlMessage);

            }
        }
    }

    Wednesday, September 16, 2015 9:20 AM

Answers

  • Thanks for your kind reply Katherine,

    But I did with this below code. it will send more than one recipients with HTML Body format.

    Use the below code if any other users need to to send mail with HTML Body.

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */

    using System;
    using System.IO;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Net.Mail;
    using System.Net;

    namespace ST_b28025465e064c28a92afa59da1b0075.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {

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

            /*
            The execution engine calls this method when the task executes.
            To access the object model, use the Dts property. Connections, variables, events,
            and logging features are available as members of the Dts property as shown in the following examples.

            To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
            To post a log entry, call Dts.Log("This is my log text", 999, null);
            To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

            To use the connections collection use something like the following:
            ConnectionManager cm = Dts.Connections.Add("OLEDB");
            cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

            Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
            
            To open Help, press F1.
        */

            public void Main()
            {
                // TODO: Add your code here
                {
                    SmtpClient mySmtpClient = new SmtpClient();
                    MailMessage myMessage = new MailMessage();
                    mySmtpClient = new SmtpClient((string)Dts.Variables["User::MailSMTPServer"].Value);
                    // variable should contain the SMTP server configured in SQL server
                    mySmtpClient.Port = 25; //SMTP Port
                    myMessage.To.Add((string)Dts.Variables["User::to"].Value);
                    //Recepient�s email Address
                    myMessage.CC.Add((string)Dts.Variables["User::CC"].Value);
                    myMessage.Subject = Dts.Variables["User::subject"].Value.ToString();
                    //string errorMsg = Dts.Variables["User::msg"].Value.ToString();

                    myMessage.Body = String.Format("<HTML><body></P><P><B>Project Cost Hrs Analysis:</B><br>The MOP provides the breakup of CGI hours and CBU hours for Finance and Accounting to make the appropriate cost allocations<br><br><b>BD Cost Hrs Analysis:</b><br>The MOP Provides analysis of BD hours entered by CGI Analysts.  The entires considered are:<li>Selecting BD option while entering TS in PMC - Region/BU is determined based on selected Slx Account CBU Book#</li><li>Some Mgmt people have BD tasks in special quarterly project 'Mgmt Leads the Way'  - Region/BU is determined as defined in the tasks</li><br><br><b>Note:</b><li>The Cost Hours are limited to timesheets entered for Oct - " + Dts.Variables["User::Prevyear"].Value.ToString() + " to " + Dts.Variables["User::MonthName"].Value.ToString() + "However the Prj Alloc Hrs shown is for the entire project</li><li>ME, MI, AB, Awards and GC Deliverables are considered and denoted in the Type column for easy reference</li><li>TS entered for Oct -" + Dts.ariables["User::Prevyear"].Value.ToString() + " to " + Dts.Variables["User::MonthName"].Value.ToString() + " - 2015 by a CGI resource in any of the deliverables as noted above is considered in the analysis.  The completion of the projects is not checked</li><br><br><left><B>Designing Business Through Information.</B></left><br><B>BOA</B></P></BODY></HTML>");
                    myMessage.IsBodyHtml = true;
                    myMessage.From = new MailAddress(Dts.Variables["User::from"].Value.ToString(), "BOA SERVER");
                    mySmtpClient.Send(myMessage);
                    //Send the Email
                    //MessageBox.Show(�Package Succedded�);
                    Dts.TaskResult = (int)ScriptResults.Success;

                }
            }
        }
    }

    • Marked as answer by Guru SQL Monday, September 21, 2015 6:26 AM
    Monday, September 21, 2015 6:26 AM

All replies

  • but i am not able to send more than one recipients with the below code.

    You can do it as in every e-mail client: Use a semicolon separate list of all recipients.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, September 16, 2015 10:15 AM
  • Ya I have tried this,

    But  I am getting error below like this.

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: The specified string is not in the form required for an e-mail address.
       at System.Net.Mime.MailBnfHelper.ReadMailAddress(String data, Int32& offset, String& displayName)
       at System.Net.Mail.MailAddress.ParseValue(String address)
       at System.Net.Mail.MailAddress..ctor(String address, String displayName, Encoding displayNameEncoding)
       at System.Net.Mail.Message..ctor(String from, String to)
       at System.Net.Mail.MailMessage..ctor(String from, String to)
       at System.Net.Mail.MailMessage..ctor(String from, String to, String subject, String body)
       at ST_b28025465e064c28a92afa59da1b0075.csproj.ScriptMain.SendMailMessage(String SendTo, String From, String Subject, String Body, Boolean IsBodyHtml, String Server)
       at ST_b28025465e064c28a92afa59da1b0075.csproj.ScriptMain.Main()
       --- End of inner exception stack trace ---
       at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
       at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    Wednesday, September 16, 2015 10:30 AM
  • Hi Guru,

    Based on my research, the problem is that you are supplying a list of addresses separated by semi-colons to the MailMessage constructor when it only takes a string representing a single address.

    To specify multiple addresses, we can split the incoming address list on the ";" character, and add them to the mail message. For more details, please refer to the following two similar threads:
    http://stackoverflow.com/questions/3209129/unable-to-send-an-email-to-multiple-addresses-recipients-using-c-sharp
    http://stackoverflow.com/questions/23484503/sending-email-to-multiple-recipients-with-mailmessage

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Thursday, September 17, 2015 9:36 AM
  • Thanks for your kind reply Katherine,

    But I did with this below code. it will send more than one recipients with HTML Body format.

    Use the below code if any other users need to to send mail with HTML Body.

    /*
       Microsoft SQL Server Integration Services Script Task
       Write scripts using Microsoft Visual C# 2008.
       The ScriptMain is the entry point class of the script.
    */

    using System;
    using System.IO;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Net.Mail;
    using System.Net;

    namespace ST_b28025465e064c28a92afa59da1b0075.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {

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

            /*
            The execution engine calls this method when the task executes.
            To access the object model, use the Dts property. Connections, variables, events,
            and logging features are available as members of the Dts property as shown in the following examples.

            To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
            To post a log entry, call Dts.Log("This is my log text", 999, null);
            To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

            To use the connections collection use something like the following:
            ConnectionManager cm = Dts.Connections.Add("OLEDB");
            cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

            Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
            
            To open Help, press F1.
        */

            public void Main()
            {
                // TODO: Add your code here
                {
                    SmtpClient mySmtpClient = new SmtpClient();
                    MailMessage myMessage = new MailMessage();
                    mySmtpClient = new SmtpClient((string)Dts.Variables["User::MailSMTPServer"].Value);
                    // variable should contain the SMTP server configured in SQL server
                    mySmtpClient.Port = 25; //SMTP Port
                    myMessage.To.Add((string)Dts.Variables["User::to"].Value);
                    //Recepient�s email Address
                    myMessage.CC.Add((string)Dts.Variables["User::CC"].Value);
                    myMessage.Subject = Dts.Variables["User::subject"].Value.ToString();
                    //string errorMsg = Dts.Variables["User::msg"].Value.ToString();

                    myMessage.Body = String.Format("<HTML><body></P><P><B>Project Cost Hrs Analysis:</B><br>The MOP provides the breakup of CGI hours and CBU hours for Finance and Accounting to make the appropriate cost allocations<br><br><b>BD Cost Hrs Analysis:</b><br>The MOP Provides analysis of BD hours entered by CGI Analysts.  The entires considered are:<li>Selecting BD option while entering TS in PMC - Region/BU is determined based on selected Slx Account CBU Book#</li><li>Some Mgmt people have BD tasks in special quarterly project 'Mgmt Leads the Way'  - Region/BU is determined as defined in the tasks</li><br><br><b>Note:</b><li>The Cost Hours are limited to timesheets entered for Oct - " + Dts.Variables["User::Prevyear"].Value.ToString() + " to " + Dts.Variables["User::MonthName"].Value.ToString() + "However the Prj Alloc Hrs shown is for the entire project</li><li>ME, MI, AB, Awards and GC Deliverables are considered and denoted in the Type column for easy reference</li><li>TS entered for Oct -" + Dts.ariables["User::Prevyear"].Value.ToString() + " to " + Dts.Variables["User::MonthName"].Value.ToString() + " - 2015 by a CGI resource in any of the deliverables as noted above is considered in the analysis.  The completion of the projects is not checked</li><br><br><left><B>Designing Business Through Information.</B></left><br><B>BOA</B></P></BODY></HTML>");
                    myMessage.IsBodyHtml = true;
                    myMessage.From = new MailAddress(Dts.Variables["User::from"].Value.ToString(), "BOA SERVER");
                    mySmtpClient.Send(myMessage);
                    //Send the Email
                    //MessageBox.Show(�Package Succedded�);
                    Dts.TaskResult = (int)ScriptResults.Success;

                }
            }
        }
    }

    • Marked as answer by Guru SQL Monday, September 21, 2015 6:26 AM
    Monday, September 21, 2015 6:26 AM