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