0

I am trying to use a task on a SSIS package to send out an email using C# as the sendmail task wouldn't work (not set up to port 25 but 587).

this is the code that I have found (I know very little for C#) and I have tried:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.Net.Mail;
#endregion

namespace ST_73a495fd8aa540bcb5fc84ccdf3b837a
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            MailMessage mail = new MailMessage();
            SmtpClient SmtpServer = new SmtpClient("smtp.office365.com");
            mail.From = new MailAddress("xxx@xx.xx");
            mail.To.Add("xxx@xx.xx");
            mail.Subject = "Test Mail - 1";
            mail.Body = "mail with attachment";

            System.Net.Mail.Attachment attachment;
            attachment = new System.Net.Mail.Attachment("x:/...test.csv");
            mail.Attachments.Add(attachment);

            SmtpServer.Port = 587;
            SmtpServer.Credentials = new System.Net.NetworkCredential("xxx@xx.xx", "testpassword");
            SmtpServer.EnableSsl = true;

            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

    }
}

when trying to run this in the SSIS package after some time I get this error:

   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()

Any ideas on what I can do to resolve this?

fireshark519
  • 175
  • 11
  • 2
    this is not the full stack trace. please edit your post and provide full stack trace. – Md. Tazbir Ur Rahman Bhuiyan Nov 20 '18 at 10:46
  • 1
    @Tazbir Bhuiyan I have editted it and added the rest of it... – fireshark519 Nov 20 '18 at 10:53
  • sorry to bother you again. the changes you made is not stack trace. stack trace is the error part you are getting( you provided some of it in the second code snippet). give the full stack trace – Md. Tazbir Ur Rahman Bhuiyan Nov 20 '18 at 10:58
  • not bothering at all @Tazbir Bhuiyan. When i try to execute the script task on the ssis package this is what I get on the execution results: DtsDebugHost.exe: DTS' has exited with code 0 (0x0). On the debugger itself it gives the following runtime error: Exception has been thrown by the target of an invocation. It then has the error I get at the bottom as part of the debugger but nothing else...I also get this on the ssis debugger Error: 0x1 at Script Task: Exception has been thrown by the target of an invocation. Task failed: Script Task – fireshark519 Nov 20 '18 at 11:06
  • this might help: https://stackoverflow.com/questions/23681298/ssis-package-cancels-instantly-on-debug – Md. Tazbir Ur Rahman Bhuiyan Nov 20 '18 at 11:16
  • Sorry but that doesn't help. On their case the problem was with the encoding on the file they are importing, my problem is with sending the email out. I have tried sending it out without the attachment as well but that is not working either – fireshark519 Nov 20 '18 at 11:23
  • can you try adding this to your code: mail.BodyEncoding = Encoding.UTF8; mail.SubjectEncoding = Encoding.UTF8; – Md. Tazbir Ur Rahman Bhuiyan Nov 20 '18 at 11:53
  • I have now tried that and I still get the same error. – fireshark519 Nov 20 '18 at 11:59
  • which line you are getting this error? – Md. Tazbir Ur Rahman Bhuiyan Nov 20 '18 at 12:00
  • sorry I don't understand your question. It gives me the exact same error message as before – fireshark519 Nov 20 '18 at 12:06
  • Sorry for the confusion. In which line of the shared code block you are getting this error. Or is it happening before or after execution of this code? That was my question – Md. Tazbir Ur Rahman Bhuiyan Nov 20 '18 at 12:07
  • It's happening after the execution of the code. I am executing the task from visual studios and while it is executing the task it brings up that error, it doesn't tell me which line the error is coming from. – fireshark519 Nov 20 '18 at 12:10
  • In this code block you didn't execute the SSIS package. you can review the enum after executing it. Are you sure you are executing SSIS package by calling the Execute() method? – Md. Tazbir Ur Rahman Bhuiyan Nov 20 '18 at 12:18
  • SSIS contains tasks (and components) that allow you to run arbitrary .NET code. That's why you don't see the Execute() method being called Tazbir. – billinkc Nov 20 '18 at 12:31
  • The first step in debugging this is to find what line is failing and the specific error being raised. Wrap the contents of your Main in a big try/catch block. In the Catch block, log what the heck is going on. I'd suggest something like `bool fireAgain = false; Dts.Events.FireInformation(0, "SCR DEBUG", myException, String.Empty, 0, ref fireAgain);` and then continue adding FireInformation in there to dump out the state of the componentry. – billinkc Nov 20 '18 at 12:37
  • Comparing your code to the first sample I found of [sending mail from o365](https://stackoverflow.com/q/6244694/181965) it looks like they specify `UseDefaultCredentials = false` They then make note of "the FROM address must exactly match the account you are sending from, or you will get a error 5.7.1 Client does not have permissions to send as this sender." Finally, it looks like when you specify your `NetworkCredential` you need to specify your domain – billinkc Nov 20 '18 at 12:42
  • billinkc I have read through that thread now and made every change I could find from there with no luck...sorry I didn't make it clear in my initial post but this is the first time I touched C# as I am more of a SQL man myself...I tried copying the code that I saw somewhere else and adapting it so...you mention to get it to log what the problem is in the catch block, other than what you wrote what else would I need to code in there to get a log of the errors? – fireshark519 Nov 20 '18 at 13:49
  • Put a break point on the script task. It might help you to understand what the actual problem is. Use F10 or F11 to step through your code when the breakpoint has been triggered – holder Nov 22 '18 at 08:21

0 Answers0