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); try { // 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"; 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(strFTPPath, strDestination, false, null); // Throw on any error transferResult.Check(); // Print results to output box foreach (TransferEventArgs transfer in transferResult.Transfers) { Dts.Events.FireInformation(0, "Files Uploaded:", "", "", 0, ref fireAgain); } session.Dispose(); } //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; }