I had to do this too, for one of my work projects recently. We used the WinSCP .NET assembly inside an SSIS Scripting Task, as this is what WinSCP also recommends as the way to achieve SFTP using WinSCP in SSIS.
See this guide - Using WinSCP .NET Assembly from SQL Server Integration Services (SSIS). It walks you through the install and setup and also contains working sample code (after you change the script to your needs of course!).
Sample code - after you reference the WinSCPnet.dll assembly - is below.
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.
HostName = (string) Dts.Variables["User::HostName"].Value,
UserName = (string) Dts.Variables["User::UserName"].Value,
Password = (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);
// 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;
}
}
}
}
