none
SSIS Error - SFTP in C# RRS feed

  • Question

  • Hi, I found this script

    using System;
    using System.Data;
    using System.Text;
    using System.Linq;
    using System.Collections;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Tamir.SharpSsh;
    
    namespace ST_be8a674357f74628a383be8a977ceef8.csproj
    {
        [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            public void Main()
            {
                string host = "sftp.xxxx.com";
                int port = 22;
                string user = "xx";
                string privateKey = @"C:\temp\sftp.ppk";
    
                SftpPlugin sftp = new SftpPlugin(host, port, user, privateKey);
                string[] files = sftp.ListFiles("test.txt");
              
                // upload a file
                bool done = sftp.UploadFile(@"C:\temp\test.txt", "xxx/");
    
    
                //Dts.TaskResult = (int)ScriptResults.Success;
            }
    
            public class SftpPlugin
            {
                private int _port;
                private string _privateSshKeyPath;
                private Tamir.SharpSsh.Sftp _tarmirSftp;
    
                public SftpPlugin(string host, string username)
                {
                    this._tarmirSftp = new Sftp(host, username);
                    this._port = 22;
                }
    
                public SftpPlugin(string host, int port, string username)
                {
                    this._tarmirSftp = new Sftp(host, username);
                    this._port = port;
                }
    
                public SftpPlugin(string host, int port, string username, string privateSshKeyPath)
                {
                    this._tarmirSftp = new Sftp(host, username);
                    this._port = port;
                    this._privateSshKeyPath = privateSshKeyPath;
                }
    
                private void Connect()
                {
                    this._tarmirSftp.Connect(this._port);
                    if (!string.IsNullOrEmpty(this._privateSshKeyPath))
                    {
                        this._tarmirSftp.AddIdentityFile(this._privateSshKeyPath);
                    }
                }
    
                // upload a file to a remote path
                public bool UploadFile(string file, string toRemotePath)
                {
                    if (!this._tarmirSftp.Connected)
                    {
                        this.Connect();
                    }
                    try
                    {
                        this._tarmirSftp.Put(file, toRemotePath);
                        return true;
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        this._tarmirSftp.Close();
                    }
                }
    
                // list the files of a remote location
                public string[] ListFiles(string remotePath)
                {
                    if (!this._tarmirSftp.Connected)
                    {
                        this.Connect();
                    }
                    string[] files = new string[] { };
                    try
                    {
                        files = this._tarmirSftp.GetFileList(remotePath).Cast<string>().ToArray();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        this._tarmirSftp.Close();
                    }
                    return files;
                }
    
            }
        }
    
    }

    But something went wrong, this is what happen:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Tamir.SharpSSH, Version=1.1.1.13, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
    File name: 'Tamir.SharpSSH, Version=1.1.1.13, Culture=neutral, PublicKeyToken=null'
       at ST_be8a674357f74628a383be8a977ceef8.csproj.ScriptMain.SftpPlugin..ctor(String host, Int32 port, String username, String privateSshKeyPath)
       at ST_be8a674357f74628a383be8a977ceef8.csproj.ScriptMain.Main()

    WRN: Assembly binding logging is turned OFF.
    To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
    Note: There is some performance penalty associated with assembly bind failure logging.
    To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

       --- End of inner exception stack trace ---
       at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
       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.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    What should I do guys :<

    Monday, July 29, 2019 12:05 PM

Answers

  • You need to install the Tamir.SharpSSH,dll on the server where the script is going to execute. 

    https://sourceforge.net/projects/sharpssh/files/

    You can probably just unzip those files and put them in a folder that is in the system path on the server. 

    • Marked as answer by Vo Hoang Ngoi Monday, July 29, 2019 3:11 PM
    Monday, July 29, 2019 12:46 PM
  • The SSH server keeps a database of public keys for each account - exact details of this depends on the server (OpenSSH keeps the public keys in ~/.ssh/authorized_keys file in each account's home directory). You can use a commercial product like Ultimate SFTP.
    // Create a new instance.
    Sftp client = new Sftp();
    // Connect to the SFTP server.
    client.Connect("myserver", 22);
    // Authenticate with a private key.
    client.Authenticate("userName", "c:\\privateKey.key", "pkeypassword");
    // It's possible to use both username/password and private key to authenticate.
    // client.Authenticate("userName", "password", "c:\\privateKey.key", "pkeypassword");
    // Do something here...
    client.DownloadFile("/my remote file.dat", "my local file");
    // Disconnect.
    client.Disconnect();

    In order to have the library work in SSIS please follow the steps below:

    1. To have our components listed in the VS References dialog, copy assemblies into C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\.
    2. Add Ultimate assemblies into Global Assembly Cache (GAC) or copy the assemblies to C:\Program Files\Microsoft SQL Server\90\DTS\Binn.
    3. To use the component in SSIS Script Task, add reference to the needed components in the Project Explorer window.
    • Marked as answer by Vo Hoang Ngoi Monday, July 29, 2019 3:11 PM
    Monday, July 29, 2019 2:50 PM

All replies

  • You need to install the Tamir.SharpSSH,dll on the server where the script is going to execute. 

    https://sourceforge.net/projects/sharpssh/files/

    You can probably just unzip those files and put them in a folder that is in the system path on the server. 

    • Marked as answer by Vo Hoang Ngoi Monday, July 29, 2019 3:11 PM
    Monday, July 29, 2019 12:46 PM
  • The SSH server keeps a database of public keys for each account - exact details of this depends on the server (OpenSSH keeps the public keys in ~/.ssh/authorized_keys file in each account's home directory). You can use a commercial product like Ultimate SFTP.
    // Create a new instance.
    Sftp client = new Sftp();
    // Connect to the SFTP server.
    client.Connect("myserver", 22);
    // Authenticate with a private key.
    client.Authenticate("userName", "c:\\privateKey.key", "pkeypassword");
    // It's possible to use both username/password and private key to authenticate.
    // client.Authenticate("userName", "password", "c:\\privateKey.key", "pkeypassword");
    // Do something here...
    client.DownloadFile("/my remote file.dat", "my local file");
    // Disconnect.
    client.Disconnect();

    In order to have the library work in SSIS please follow the steps below:

    1. To have our components listed in the VS References dialog, copy assemblies into C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\.
    2. Add Ultimate assemblies into Global Assembly Cache (GAC) or copy the assemblies to C:\Program Files\Microsoft SQL Server\90\DTS\Binn.
    3. To use the component in SSIS Script Task, add reference to the needed components in the Project Explorer window.
    • Marked as answer by Vo Hoang Ngoi Monday, July 29, 2019 3:11 PM
    Monday, July 29, 2019 2:50 PM