1

I am using SSIS 12, SQL Server 2012, Visual Studio 2012 and .net 4.

My SSIS script task code(for sending mail):

// Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */

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

namespace ST_20fdf0b00e2949fda158e6680c127473
{
        public void Main()
        {
            SmtpClient smtp = new SmtpClient("smtp.gmail.com", 587);
            smtp.EnableSsl = true;
            smtp.UseDefaultCredentials = false;
            smtp.Credentials = new NetworkCredential("raihanuahmed@gmail.com", "password");

            MailMessage msg = new MailMessage();
            msg.From = new MailAddress("raihanuahmed@gmail.com");
            msg.To.Add(new MailAddress("raihan.csse.aiub@gmail.com"));
            msg.Subject = "AW2012 ETL Process Complite";
            msg.Body = string.Format("{0} records loaded into DimProduct", 0);

            smtp.Send(msg);

            Dts.TaskResult = (int)ScriptResults.Success;
        }

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

After executing this email sending script, I am always getting

"DTS Script :Runtime 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()

I have uploaded my problem into Youtube here is the link:

DTS Script :Runtime Error

Here is my project link in Dropbox:

EmailScript

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RU Ahmed
  • 558
  • 4
  • 23
  • First comment out lines until you work out which line is throwing the runtime error. – Nick.Mc Nov 11 '15 at 04:27
  • Sorry, cant able to trace it , but i have uploaded my problem in Youtube here is the link :https://www.youtube.com/watch?v=QMpLsSoW4WU&feature=youtu.be and here is my project link : https://www.dropbox.com/s/o0jb4u8ch75hv7v/ScriptTask.rar?dl=0 – RU Ahmed Nov 11 '15 at 05:45
  • 1
    comment out `smtp.Send(msg);`. Do you still get the error? – Nick.Mc Nov 11 '15 at 06:51
  • 1
    This describes how to set a breakpoint: https://msdn.microsoft.com/en-AU/library/ms140033(v=sql.110).aspx you need to set a breakpoint, step through the code and find a more useful error. "Runtime error" is not useful but if you capture the error while it's running you can usually drill in and get a useful error. – Nick.Mc Nov 11 '15 at 08:34
  • 1
    OMG !!!! ,, if i comment out smtp.Send(msg); ,, I do not get any error ... But my problem isn't fixed yet as i cant able to receive the message – RU Ahmed Nov 11 '15 at 17:19
  • 1
    Warning: 0x0 at Task Succesful Email: Cannot attach the VSTA debugger. Re-starting the debugging may resolve the problem. Error: 0x1 at Task Succesful Email: Exception has been thrown by the target of an invocation. Task failed: Task Succesful Email – RU Ahmed Nov 11 '15 at 17:31
  • 1
    You need a more detailed error message but it looks like debugging won't help. Uncomment `smtp.Send(msg);` and add this after it: `catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }`. If this is valid code (not sure) it should pop up a more detailed error message – Nick.Mc Nov 12 '15 at 09:51
  • and something to consider... do you have an internal email server? because you might have more luck sending email to that. – Nick.Mc Nov 12 '15 at 09:52
  • 1
    thank you for your replay, when i use try-catch , a window pop us say **the smtp server requires a secure connection or the client was not authenticated. the server response was: 5.5.1 authentication required** – RU Ahmed Nov 12 '15 at 22:54
  • 1
    But interesting thing is at the same time i have revived an email which subject is **Sign-in attempt prevented** and in body it say **Hi Raihan Uddin, Someone just tried to sign in to your Google Account raihanuahmed@gmail.com from an app that doesn't meet modern security standards.We strongly recommend that you use a secure app, like Gmail, to access your account. All apps made by Google meet these security standards. Using a less secure app, on the other hand, could leave your account vulnerable. Google stopped this sign-in attempt, but you should review your recently used devices:** – RU Ahmed Nov 12 '15 at 22:54
  • 1
    I searched your error message and found this: http://stackoverflow.com/questions/18503333/the-smtp-server-requires-a-secure-connection-or-the-client-was-not-authenticated which says to log in to gmail and _enable Less Secure Sign-In in your google account_. Try that – Nick.Mc Nov 12 '15 at 22:57
  • 1
    "sorry ,I do not have an internal email server" :( :( :( i am just using Gmail – RU Ahmed Nov 12 '15 at 23:02
  • 1
    Here is the video shot of the current condition of my problem (after using try catch): [After try catch SSIS Script Task for sending mail Problem]https://www.youtube.com/watch?v=CExG8CvUD2Q&feature=youtu.be – RU Ahmed Nov 12 '15 at 23:02

1 Answers1

2

I have found the solution :) Many Many thanks to Mr.Nick.McDermaid ...... solution is :

Change account access for less secure apps https://support.google.com/accounts/answer/6010255

RU Ahmed
  • 558
  • 4
  • 23
  • 1
    The try/catch is just to get a more useful error. You need to take this out before you deploy your package or it's going to pop up messageboxes and freeze. – Nick.Mc Nov 12 '15 at 23:50