Post a reply

Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)


Topic review


Re: Different way to use WinSCP within SSIS using a Script task

Thanks for sharing this.
Based on this example, I have created article on using the WinSCP .NET assembly from SSIS:

Different way to use WinSCP within SSIS using a Script task

Must have WinSCP installed
run gacutil -i "C:\Program Files\WinSCP\WinSCP.dll" within the command prompt, as an admin

Within your SSIS Script task, add a reference to the dll file using the browse task

add using WinSCP;

your Main() method can now look similar like the following:

public void Main()
string strFTPPath = Dts.Variables["FTPFolderPath"].Value.ToString();
string strDestination = Dts.Variables["DestinationFolder"].Value.ToString();
bool fireAgain = false;

strFTPPath = strFTPPath + @"/";

Dts.Events.FireInformation(0, "FTP Path Used:", strFTPPath, "", 0, ref fireAgain);
Dts.Events.FireInformation(0, "Destination Path Used:", strDestination, "", 0, ref fireAgain);

// Setup session options
SessionOptions sessionOptions = new SessionOptions
Protocol = Protocol.Sftp,
HostName = "FTP server",
UserName = "User",
Password = "Password",
SshHostKey = "ssh-rsa xxxx xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"

using (Session session = new Session())
// Connect

session.ExecutablePath = @"C:\Program Files\WinSCP\WinSCP.exe";

// Upload files
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;

TransferOperationResult transferResult;
//transferResult = session.PutFiles(@"d:\toupload\*", "/home/user/", false, transferOptions);
transferResult = session.GetFiles(strFTPPath, strDestination, false, null);

// Throw on any error

// Print results to output box
foreach (TransferEventArgs transfer in transferResult.Transfers)
Dts.Events.FireInformation(0, "Files Uploaded:", "", "", 0, ref fireAgain);

//return 0;
catch (Exception e)
Dts.Events.FireInformation(0, "Error: ", e.Message.ToString(), "", 0, ref fireAgain);
Dts.TaskResult = (int)ScriptResults.Failure;
//return 1;

Dts.TaskResult = (int)ScriptResults.Success;