SSIS - Using WinSCP to SFTP

In SSIS package, drag the 2 tasks as shown below:

 Define a list of variables:

sftpHostAddress uses an expression = @[User::sftpHostPrefix] + @[User::sftpUsername] + ":" + @[User::sftpPassword] + "@" + @[User::sftpHost] + ( @[User::sftpHostPort] != ""?":" + @[User::sftpHostPort]:"") + " -hostkey=\"" + @[User::sftpFingerprint] + "\""

The script task looks like this:

And its C# script looks like this:

        public void Main()

        {

            String strDelete = "";

            // TODO: Add your code here

            // remove file after download

            if (Dts.Variables["sftpRemoveAfterDownload"].Value.ToString() == "Y")

                strDelete = "-delete ";

            // Build the WinSCP "Script" in a string variable.

            Dts.Variables["sftpCommand"].Value =

                      "option batch abort\r\n" +

                      "option confirm off\r\n" +

                      "open " + Dts.Variables["sftpHostAddress"].Value.ToString() + "\r\n" +

                      "cd \"" + Dts.Variables["sftpRemoteFolder"].Value.ToString() + "\"\r\n" +

                      "lcd \"" + Dts.Variables["sftpLocalFolder"].Value.ToString() + "\"\r\n" +

                      "get -preservetime " + strDelete + "\"" + Dts.Variables["sftpFileNameSpec"].Value.ToString() + "\"\r\n" +

                      "exit\r\n";

               

            //if (Dts.Variables["sftpResults"].Value.ToString() == "Testing")

            //    MessageBox.Show(Dts.Variables["sftpCommand"].Value.ToString());

            // Initialize the result of this command to an empty string.

            Dts.Variables["sftpResults"].Value = "";

            //Tell BIDS that all is well..

            Dts.TaskResult = (int)ScriptResults.Success;

        }

The Execute Process Task looks like (the yellow highlight is only needed for debugging purpose):