SQLTeam.com | Weblogs | Forums

Need Help with SSIS "Download file from SFTP"


#1

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.
(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;
        }
    }
}

}


#2

https://winscp.net/eng/docs/guide_ssis

This should work.