SQLTeam.com | Weblogs | Forums

Need Help with SSIS "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_ssis
One 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.
//////SshHostKeyFingerprint = (string)Dts.Variables["User::SshHostKeyFingerprint"].Value

            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

                // 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

                // 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;




This should work.