I'm trying to connect to a SFTP
server via an SSIS
package. The package executes WinSCP
with the following connection string in a .txt
file:
open sftp://username:fc$#[email protected]:22
However the package keeps failing without being able to connect. Is it something to do with the special characters in the password?
I am able to connect to a different SFTP if I replace the string so I know it must be something to do with the syntax above. I've tried putting double quotes around the string as follows without any success:
open "sftp://username:fc$#[email protected]:22"
In the script you need at least to: Replace mysession argument to open command with specification of SFTP connection in form sftp://username:password@hostname/ or use name of site. Replace host key fingerprint after -hostkey switch of open command with actual fingerprint of your SFTP/SSH server host key;2.
You may be aware of the FTP task in SSIS which allows us to copy or paste files to/from a FTP site, but unfortunately SSIS doesn't support communication over SFTP. A work around for this will be to use PSFTP through an Execute Process Task in SSIS to download the file to our local machine.
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;
}
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With