Script to Upload file to sFTP using winscp causing error

Question

I'm getting this error:

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

Code:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Renci.SshNet;
using System.IO;
using System.Threading;
#endregion

namespace ST_e4fe7cc5f9914a52b66a9e0bb572fa3b
{

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {


        public void Main()
        {
            var UserName = Dts.Variables["User::SFTPUserName"].Value.ToString();
            var HostName = Dts.Variables["User::SFTPServerName"].Value.ToString();
            var KeyFilePath = Dts.Variables["User::SFTPKeyFilePath"].Value.ToString();
            var UploadPath = Dts.Variables["User::SFTPDirectoryPath"].Value.ToString();
            var OutBoundFilePath = Dts.Variables["User::OutBoundFilePath"].Value.ToString() ;
            int RetryInterval = Convert.ToInt32(Dts.Variables["User::RetryInterval"].Value.ToString());
            int RetryLimit = Convert.ToInt32(Dts.Variables["User::RetryLimit"].Value.ToString());

            //Create Connection   
            PrivateKeyFile objKeyFile = new PrivateKeyFile(KeyFilePath);
            SftpClient objSFTPclient = new SftpClient(HostName, UserName, objKeyFile);
            objSFTPclient.BufferSize = 32000;

            //Upload Files
            try
            {
                objSFTPclient.Connect();
                UploadFiles(objSFTPclient, UploadPath, OutBoundFilePath);
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                //An error occurred.
                Dts.Events.FireError(0, "SFTP", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
                //throw;
            }

            finally
            {
                if (objSFTPclient != null && objSFTPclient.IsConnected)
                {
                    objSFTPclient.Disconnect();
                    objSFTPclient.Dispose();
                }
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        public void UploadFiles(SftpClient objSFTPclient, String UploadPath, String OutBoundFilePath)
        {
            string FTPFolderName = Dts.Variables["User::varRRODataSource"].Value.ToString();

            if (!(objSFTPclient.Exists(UploadPath + "/" + FTPFolderName)))
            {
                objSFTPclient.CreateDirectory(UploadPath + "/" + FTPFolderName);
            }

            if (objSFTPclient.Exists(UploadPath + "/" + FTPFolderName))
            {
                if (bool.Parse(Dts.Variables["User::EncryptDecryptEnabled"].Value.ToString()))
                {
                    var fs = new FileStream(OutBoundFilePath + "\\" + Dts.Variables["User::CompressedFileName"].Value.ToString() + ".zip.aes", FileMode.Open);
                    objSFTPclient.UploadFile(fs, UploadPath + "//" + FTPFolderName + "//" + Dts.Variables["User::CompressedFileName"].Value.ToString() + ".zip.aes");



                    System.IO.File.Create(OutBoundFilePath + "\\done.txt").Close();
                    using (var fs1 = File.OpenRead(OutBoundFilePath + "\\done.txt"))
                    {
                        objSFTPclient.UploadFile(fs1, UploadPath + "//" + FTPFolderName + "//done.txt");
                    }
                }
                else
                {
                    var fs = new FileStream(OutBoundFilePath + "\\" + Dts.Variables["User::CompressedFileName"].Value.ToString() + ".zip", FileMode.Open);
                    objSFTPclient.UploadFile(fs, UploadPath + "/" + FTPFolderName + "/" + Dts.Variables["User::CompressedFileName"].Value.ToString() + ".zip");
                    objSFTPclient.Create(UploadPath + "/" + FTPFolderName +      "/done.txt");
                }
            }
        }

        #region ScriptResults declaration

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

    }
}

Please add more information to your question. How do I ask a good question? — Leon
Hi Leon, Im executing this via SSIS Data tools, when the code hits this Script step it gives me the error that i mentioned above — Arumugam Ramc

plditallo plditallo · Accepted Answer · 2016-11-30T23:49:17

@Arumugam, In the script task, put in breakpoints. If you are failing before your 1st try/catch, place your first breakpoint on this line:

SftpClient objSFTPclient = new SftpClient(HostName, UserName, objKeyFile);

If the stack trace you posted happens within the tray/catch, place your first breakpoint on this line:

objSFTPclient.Connect();

I am not Renci.SshNet expert, but it appears that the code is attempting to open a shell through SSIS, so the invocation fails. If Renci.SshNet has a command line for sftp file transfer--which is what it looks like you're attempting to do, use that utilizing the execute process task.

Script to Upload file to sFTP using winscp causing error

1 Answers