I finally got this figured out, fixed, and it works fine now.
The log from Winspc initially did not generate because by default the interface has the log as Normal, but it should be changed to either bug1 or bug2 to have the log generates when there is an error. So, when I changed it, it generated the error process. Also, our sftp this morning went through a change of password right exactly the time I was testing on this, so this is why it ran but it didn't do anything, and without using the log, I could not figure out why it did not work.

Anyway, in summary, for those of you who wants to use winscp to connect to SFTP (a task component that is currently not available in SSIS) using SSIS, below is how you can set it up as I already mentioned earlier. Use Execute Process Task >> Edit >> Process option, and put the following information: Executable: C:\WINDOWS\System32\CScript.exe Arguments: /nologo LoadRecentFiles.js Working Directory: C:\MyFolder\Scripts\. Then, your LoadRecentFiles.js (or whatever the name you name this file) is script of downloading the latest file that is available from this website, https://winscp.net/eng/docs/script_download_most_recent_file. Just change your username, password, directory locations, then you all set. Make sure you keep the trailing slash where it said so or it won't work.

The only thing left I have to do now is to hide the cscript console while it is executing and I believe there is an instruction on that as well on this website.

Thanks prikryl very much for showing me how to configure and look at the log. Your code works wonderfully. Keep up the great work.

Thanks a lot for responding. Let me re-explain my problem. I'm using SSIS 2008 to make a call to SFTP server through WinSCP 4.2.9. In my SSIS package, specifically in the Execute Process Task >> Edit >> Process option, I put the following information: Executable: C:\WINDOWS\System32\CScript.exe
Arguments: /nologo LoadRecentFiles.js, Working Directory: C:\MyFolder\Scripts\. The LoadRecentFiles.js is the exact script that you have on this website for downloading the latest file, The only difference is my login and directory info. When I ran this SSIS package, I can see the CScript's console pops up briefly while SSIS was running, and right after that SSIS finished as it ran successfully; however, it downloaded NO file. So, my earlier question was is it possible to pause the CScript's console inside this javascript so I can see what it was executing? And if so, how? I wanted to log this process like you requested so I can show it to you, but the logging instruction is for doing the operation through WinSCP interface. But I'm doing this through SSIS, so configure the log using WinSCP interface doesn't work. Thus, my second question is how can I do the logging inside the javascript, so I can send that information to you to see why Cscript and SSIS package ran successfully, but it doesn't download the file.

Please post a full log file showing the problem.

I found out that I put double forward slashes, cscript //nologo loadlatestfiles.js, so I changed it to a single foward slash, and the script ran non-interactively (only the window console pops up, but not the SFTP interface). SSIS ran this script sucessfully now, however, no file was downloaded. Below is the script I used, I changed mostly only the variables section. Because the script was running successfully, no error, so I don't know why the new file was not downloaded. I can see the window console pops up with code it ran, but I can't pause it to see what it was doing. Is there a way to pause the command line at the end inside javascript code to see what it was executing?

// Configuration

// Local path to download to (keep trailing slash)
var LOCALPATH = "c:\\MyFolder\\";
// Remote path to search in (keep trailing slash)
var REMOTEPATH = "/myusername:";
// Mask of files to search for
var FILEMASK = "MyFilePartialName_*.txt";
// Session to connect to
var SESSION = "sftp://myusername:password@hostname";
// Path to winscp.com
var WINSCP = "C:\\Program Files (x86)\\WinSCP\\WinSCP.com";

var filesys = WScript.CreateObject("Scripting.FileSystemObject");
var shell = WScript.CreateObject("WScript.Shell");

var logfilepath = filesys.GetSpecialFolder(2) + "\\" + filesys.GetTempName() + ".xml";

var exec;

// run winscp to get list of file in the remote directory into XML log
exec = shell.Exec("\"" + WINSCP + "\" /log=\"" + logfilepath + "\"");
exec.StdIn.Write(a
"option batch on\n" +
"option confirm off\n" +
"open \"" + SESSION + "\"\n" +
"ls \"" + REMOTEPATH + FILEMASK + "\"\n" +
"exit\n");

// wait until the script finishes
while (exec.Status == 0)
{
WScript.Sleep(100);
WScript.Echo(exec.StdOut.ReadAll());
}

if (exec.ExitCode != 0)
{
WScript.Echo("Error retrieving list of files");
WScript.Quit(1);
}

// look for log file
var logfile = filesys.GetFile(logfilepath);

if (logfile == null)
{
WScript.Echo("Cannot find log file");
WScript.Quit(1);
}

// parse XML log file
var doc = new ActiveXObject("MSXML2.DOMDocument");
doc.async = false;
doc.load(logfilepath);

doc.setProperty("SelectionNamespaces",
"xmlns:w='https://winscp.net/schema/session/1.0'");

var nodes = doc.selectNodes("//w:file");

// find the latest file
var filenameLatest = null;
var modificationLatest = null;
for (var i = 0; i < nodes.length; ++i)
{
var filename = nodes[i].selectSingleNode("w:filename/@value");
var modification = nodes[i].selectSingleNode("w:modification/@value");
if ((filename != null) &&
(filename.value != ".") &&
(filename.value != "..") &&
(modification != null))
{
// can compare timestamps stringwise
if ((modificationLatest == null) ||
(modificationLatest < modification.value))
{
modificationLatest = modification.value;
filenameLatest = filename.value;
}
}
}

// no file in the log
if (filenameLatest == null)
{
WScript.Echo("No file found");
WScript.Quit(0);
}

// run winscp to download the latest file
exec = shell.Exec("\"" + WINSCP + "\"");
exec.StdIn.Write(
"option batch on\n" +
"option confirm off\n" +
"open \"" + SESSION + "\"\n" +
"option transfer binary\n" +
"get \"" + REMOTEPATH + filenameLatest + "\" \"" + LOCALPATH + "\"\n" +
"exit\n");

// wait until the script finishes
while (!exec.StdOut.AtEndOfStream)
{
WScript.Echo(exec.StdOut.ReadAll());
}

if (exec.ExitCode != 0)
{
WScript.Echo("Error downloading " + filenameLatest);
WScript.Quit(1);
}

From this website, I followed the instruction on how to get the file download from SFTP site using SSIS package, and it works fine. But I also need to make sure I download only the latest file, so I looked at the javascript code that is also available on this website. https://winscp.net/eng/docs/script_download_most_recent_file. I use the script as is, and only changed to my directories and connection information. However, when I tried to run the code inside SSIS using cscript.exe as the site suggested it (cscript /nologo loadlatestfiles.js), the SFTP interface keeps pop up forcing me to enter the user/pasword manually and download the file manually, even though I did change the script to put in option batch on and option confirm off, yet the SFTP interface still pops up. Shouldn't it work as a non-interactive? The code I put inside SSIS Execute Process Task is like this :
-- Executable: C:\WINDOWS\System32\CScript.exe
-- Argument: /nologo loadlatestfiles.js
-- Current Directory: C:\Myfolder\Scripts\
I tried to run this same code on command line, and the same thing happens - SFTP login interface would pops up. I'm using window command line, SSIS/VS 2008, winscp 4.2.9. So, my question is how can I call this javascript from SSIS non-interactively.