SSIS Send Mail task script with Visual Studio 2010

0

I am having a challenge with this send mail script in visual studio 2010.

Here is the script:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
using System.Text.RegularExpressions;
using System.Net.Mail;
using System.IO;
namespace ST_dd466aa1cac943eb887bc0d48f753e68
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to 
         * either the list contained in the ReadOnlyVariables property or the list contained in 
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and 
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         * 
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         * 
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         * 
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *  
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         * 
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         * 
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         * 
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         * 
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic 
         * "Working with Connection Managers Programatically" for details.
         * 
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


        /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        /// 

        public void Main()
        {
            string sSubject = "Test Subject";
            string sBody = "Test Message";
            int iPriority = 2;

            if (SendMail(sSubject, sBody, iPriority))
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                //Fails the Task
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        public bool SendMail(string sSubject, string sMessage, int iPriority)
        {
            try
            {
                string sEmailServer = Dts.Variables["sEmailServer"].Value.ToString();
                string sEmailPort = Dts.Variables["sEmailPort"].Value.ToString();
                string sEmailUser = Dts.Variables["sEmailUser"].Value.ToString();
                string sEmailPassword = Dts.Variables["sEmailPassword"].Value.ToString();
                string sEmailSendTo = Dts.Variables["sEmailSendTo"].Value.ToString();
                string sEmailSendCC = Dts.Variables["sEmailSendCC"].Value.ToString();
                string sEmailSendFrom = Dts.Variables["sEmailSendFrom"].Value.ToString();
                string sEmailSendFromName = Dts.Variables["sEmailSendFromName"].Value.ToString();

                SmtpClient smtpClient = new SmtpClient();
                MailMessage message = new MailMessage();

                MailAddress fromAddress = new MailAddress(sEmailSendFrom, sEmailSendFromName);

                //You can have multiple emails separated by ;
                string[] sEmailTo = Regex.Split(sEmailSendTo, ";");
                string[] sEmailCC = Regex.Split(sEmailSendCC, ";");
                int sEmailServerSMTP = int.Parse(sEmailPort);

                smtpClient.EnableSsl = true;
                smtpClient.Host = sEmailServer;
                smtpClient.Port = sEmailServerSMTP;

                System.Net.NetworkCredential myCredentials =
                   new System.Net.NetworkCredential(sEmailUser, sEmailPassword);
                smtpClient.Credentials = myCredentials;

                message.From = fromAddress;

                if (sEmailTo != null)
                {
                    for (int i = 0; i < sEmailTo.Length; ++i)
                    {
                        if (sEmailTo[i] != null && sEmailTo[i] != "")
                        {
                            message.To.Add(sEmailTo[i]);
                        }
                    }
                }

                if (sEmailCC != null)
                {
                    for (int i = 0; i < sEmailCC.Length; ++i)
                    {
                        if (sEmailCC[i] != null && sEmailCC[i] != "")
                        {
                            message.To.Add(sEmailCC[i]);
                        }
                    }
                }

                switch (iPriority)
                {
                    case 1:
                        message.Priority = MailPriority.High;
                        break;
                    case 3:
                        message.Priority = MailPriority.Low;
                        break;
                    default:
                        message.Priority = MailPriority.Normal;
                        break;
                }

                //You can enable this for Attachments.  
                //SingleFile is a string variable for the file path.
                //foreach (string SingleFile in myFiles)
                //{
                //    Attachment myAttachment = new Attachment(SingleFile);
                //    message.Attachments.Add(myAttachment);
                //}


                message.Subject = sSubject;
                message.IsBodyHtml = true;
                message.Body = sMessage;

                smtpClient.Send(message);
                return true;
            }
            //catch (Exception ex)
            //{

            //    return false;
            //}

            catch (Exception ex)
            {
                Dts.Events.FireError(-1, "ST_dd466aa1cac943eb887bc0d48f753e68", ex.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
                return false;
            }
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

and here is the error I get:

SSIS package "C:\Users\xxx\xxxx\visual studio 2010\projects\xxxx\xxxx\xxx.dtsx" starting. Error: 0xFFFFFFFF at Script Task, ST_dd466aa1cac943eb887bc0d48f753e68: System.Net.Mail.SmtpException: The operation has timed out. at System.Net.Mail.SmtpClient.Send(MailMessage message) at ST_dd466aa1cac943eb887bc0d48f753e68.ScriptMain.SendMail(String sSubject, String sMessage, Int32 iPriority) Error: 0x6 at Script Task: The script returned a failure result. Task failed: Script Task

SSIS package "C:\Users\xxx\xxxx\visual studio 2010\projects\xxxx\xxxx\xxx.dtsx" finished: Success.

Secondly, I would like to have an attachment as a variable as well.

Between, I am using godaddy email, windows server 2012R2 and visual studio 2010.

c#
visual-studio
visual-studio-2010
ssis
sendmail
asked on Stack Overflow Mar 29, 2018 by Tunde • edited Aug 27, 2019 by Dale Burrell

1 Answer

0

For your attachments you can do this where EmailAttachmentPaths is a string with the paths comma delimited

// this is to add multipel attachments ","
            string[] ToMuliPaths = EmailAttachmentPaths.Split(',');
            foreach (string ToPathId in ToMuliPaths)
            {
                // only add email if not blank
                if (ToPathId.Trim() != "")
                    mailMessage.Attachments.Add(new Attachment(ToPathId));
            }// end for loop
answered on Stack Overflow Mar 29, 2018 by Brad

User contributions licensed under CC BY-SA 3.0