Error in C# script task in SSIS when running code for email (POP3)

Question

I have an email script task that I am attempting to use to pass variables to through an SSIS script task. I have made sure that I spelled everything right and my code compiles. I get this error:

Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) 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 Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

I will admit to being a newbie here, and I apologize if there is an easy solution.

I have added the following two namespaces:

using System.Net;
using System.Net.Mail;

In the code below, I am hard coding my username and password for the credentials. I can change that to a variable (I think.)

When I hard code the values to the variables directly in the code, it still gives the same error. The code builds and cleans just fine, so any assistance is appreciated. Thank you.

public void Main()
    {
        // TODO: Add your code here
        {

            string SendMailTo = Dts.Variables["SendMailTo"].Value.ToString();
            string SendMailFrom = Dts.Variables["SendMailFrom"].Value.ToString();
            string sSubject = Dts.Variables["sSubject"].Value.ToString();
            string sBody = Dts.Variables["sBody"].Value.ToString();
            string SmtpServer = Dts.Variables["SmtpServer"].Value.ToString();





            SendMailMessage(SendMailTo, SendMailFrom, sSubject, sBody, false, SmtpServer);

            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 = IsBodyHtml;

        mySmtpClient = new SmtpClient(Server);
        mySmtpClient.Credentials = new System.Net.NetworkCredential("myusername", "mypassword"); ;
        mySmtpClient.Send(htmlMessage);

    }

    }

    #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

}

David David · Accepted Answer · 2017-07-30T02:38:22

Rookie mistake! Code didn't work in a regular C# console. Debugged it there, made the necessary adjustments, and presto! My apologies for the spam.

Error in C# script task in SSIS when running code for email (POP3)

1 Answers