SSIS "Script Task" Download File from SFTP

  • Hi Guys,

    I need help, I am not good in C#. Below is C# code that I am using to download the file from SFTP. The code was original to upload the file on SFTP. However, I change it a little bit. I am using this C# code in SSIS "Script Task". Could you please anyone help me where I am doing wrong or show me the right path that would be great. Here is the link where I grab this code. https://winscp.net/eng/docs/library_ssisOne more question, In C# code, am I able to connect SFTP without using SshHostKeyFingerprint ?
    Thanks in advance.

    using System;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
    //using System.AddIn;
    using WinSCP;

    namespace ST_5a30686e70c04c5a8a93729fd90b8c79.csproj
    {
      //[AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
      public partial class ScriptMain : VSTARTScriptObjectModelBase
      {
        public void Main()
        {
         // Setup session options
         SessionOptions sessionOptions = new SessionOptions
         {
          Protocol = Protocol.Sftp,
          // To setup these variables, go to SSIS > Variables.
          // To make them accessible from the script task, in the context menu of the task,
          // choose Edit. On the Script task editor on Script page, select ReadOnlyVariables,
          // and tick the below properties.
          (string)Dts.Variables["User::HostName"].Value,
          (string)Dts.Variables["User::UserName"].Value,
          (string)Dts.Variables["User::Password"].Value,
          //////SshHostKeyFingerprint = (string)Dts.Variables["User::SshHostKeyFingerprint"].Value
         };

         try
         {
          using (Session session = new Session())
          {
            // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
            // you need to set path to WinSCP.exe explicitly, if using non-default location.
            session.ExecutablePath = @"C:\WinSCP\winscp.exe";

            // Connect
            session.Open(sessionOptions);

            // Upload files
            TransferOptions transferOptions = new TransferOptions();
            transferOptions.TransferMode = TransferMode.Binary;

            TransferOperationResult transferResult;
            ///transferResult = session.PutFiles(@"d:\toupload\*", "/home/user/", false, transferOptions);

            transferResult = session.GetFiles(@"C:\\BackUp\\*", "/home", false, transferOptions);

            // Throw on any error
            transferResult.Check();

            // Print results
            bool fireAgain = false;
            foreach (TransferEventArgs transfer in transferResult.Transfers)
            {
             Dts.Events.FireInformation(0, null,
              string.Format("Upload of {0} succeeded", transfer.FileName),
              null, 0, ref fireAgain);
            }
          }

          Dts.TaskResult = (int)DTSExecResult.Success;
         }
         catch (Exception e)
         {
          Dts.Events.FireError(0, null,
            string.Format("Error when using WinSCP to upload files: {0}", e),
            null, 0);

          Dts.TaskResult = (int)DTSExecResult.Failure;
         }
        }
      }
    }

  • You asked for someone to say what you are doing wrong without saying what was or was not taking place when you tried to run this package.  Does it give you an error?   Keep in mind, that if one of your changes was to take out the SSH Fingerprint part, you may have effectively disabled the security piece and thus any attempt to log in to the SFTP site may fail for that reason, but without knowing what you actually tried and what happened when you did, we have nothing but guesswork to rely on.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson sorry, if my question is not clear. I thought it is pretty straightforward. I am using Script Task >>C# script to download the file from SFTP. Above is the code that I am using. However, I am getting error everytime I run Script Task. Here is the 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.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

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

       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()"

    Is anyone can help me? Feel free to let me know If my question still not clear.
  • 1. First you need to make sure WinSCPnet.dll to Global Assembly Cache (GAC). For SSIS you need to register your WinSCPnet.dll to Global Assembly Cache (GAC).
    2. to download file from SFTP check this Session.GetFiles Method Example

    Hope it helps.

  • rocky_498 - Monday, October 9, 2017 10:38 PM

    sgmunson sorry, if my question is not clear. I thought it is pretty straightforward. I am using Script Task >>C# script to download the file from SFTP. Above is the code that I am using. However, I am getting error everytime I run Script Task. Here is the 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.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

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

       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()"

    Is anyone can help me? Feel free to let me know If my question still not clear.

    I'll defer to those with more exposure to C# than I do.   twindevil posted what is likely good advice.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply