Exception has been thrown by the target of an invocation (SSIS & WinSCP & C#)

Question

I'm getting this error message while trying to execute my SCRIPT TASK in SSIS (I'm using VS 2019 and C#).

Been trying for a while it seems it wont work.

Error message: Exception has been thrown by the target of an invocation

Details:

   in System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   in System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   in System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   in System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   in Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

This is my code:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Linq;
using WinSCP;
#endregion

namespace ST_2cfb1a0997cf7b332da4453v3g1132g
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
            public void Main()
            {

                    SessionOptions sessionOptions = new SessionOptions
                    {
                        Protocol = Protocol.Sftp,
                        HostName = "xxxxxxxxxxxxxxxxxxxxxxxxx",
                        UserName = "xxxxxxxxxxxxxxxxxxxxxxxxx",
                        Password = "xxxxxxxxxxxxxxxxxxxxxxxxx",
                    };

                    using (Session session = new Session())
                    {
                        session.Open(sessionOptions);

                        const string externalPath = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
                        const string PathLocal = @"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

                        RemoteDirectoryInfo directoryInfo = session.ListDirectory(externalPath);

    
                        RemoteFileInfo latest =
                            directoryInfo.Files
                                .Where(file => !file.IsDirectory)
                                .OrderByDescending(file => file.LastWriteTime)
                                .FirstOrDefault();

                        if (latest == null)
                        {
                            throw new Exception("Error, not found");
                        }

                        session.GetFiles(
                            externalPath.EscapeFileMask(latest.FullName), PathLocal).Check();
                    }

            }

        #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

    }
}

What is going on in here?

Am i doing anything wrong?

See [SSIS C# 2012 Script Task referring WinSCPnet.dll fails when run from SQL Server Agent with “Exception has been thrown by the target of an invocation”](https://stackoverflow.com/q/34345160/850848) + Do some logging as the [WinSCP SSIS example](https://winscp.net/eng/docs/library_ssis) shows! — Martin Prikryl, Apr 05 '21 at 06:16

Marc Gravell · Accepted Answer · 2021-04-05T05:26:17.203

That sounds like TargetInvocationException. The bad news is: we can't tell you what the underlying error was. The good news is the runtime can. With all exceptions, it is a good idea to catch them and look at the inner exception(s). Consider:

try {
    // Your failing code
} catch (Exception ex) {
    while (ex is not null) {
        LogSomewhere($"{ex.Message} ({ex.GetType().Name})");
        ex = ex.InnerException;
    }
    // Todo: decide whether you want to rethrow ("throw") the
    // exception, vs consider it handled
}

You should find that this outputs additional useful information.

Note: where to catch exceptions is a bit harder to define - you shouldn't swallow an exception unless:

you understand the specific problem and have handled it, or
you are happy to just work on a "best efforts" basis, or
your method with a catch is top-level code - a UI event-handler, or a thread-start, etc; if you don't do something, the application will terminate

It is reasonable, however, to catch an exception to log/inspect it, if you rethrow, or throw a new more contextual/specific exception (in which case you should probably include the original exception as the inner-exception of your new exception).

I solved this by replacing *public void Main()*. I used *public int Main()* and it did not show any error but it is not working properly: https://stackoverflow.com/questions/66958820/ssis-script-task-running-but-not-doing-anything-c-winscp?noredirect=1#comment118366358_66958820 — JustToKnow, Apr 06 '21 at 09:13
@programming_amazing that doesn't "solve" anything; once again, you need to log the **inner exception** - it is already trying to tell you what the problem is, but you are ignoring it — Marc Gravell, Apr 06 '21 at 09:15
Marc, i was able to print the error messages, the error itself is a different story but your answer helped me a lot!. Thanks. — JustToKnow, Apr 10 '21 at 00:13

Exception has been thrown by the target of an invocation (SSIS & WinSCP & C#)

1 Answers1