Exit Code Issues in SSIS

Have checked the site and found no record of this so posting to see if anyone has any help and has resolved this issue or if something dodgy is happening in this application.

I've managed to get an 'Execute Process Task' package in SSIS to call the winSCP.com application and run a command against it to use a script, this then guides winSCP to transfer a file onto a remote SFTP location.
I'm currently only testing so far using a local FTP but in theory all would work when I move this to an SFTP platform.

This issue comes when the file has successfully transferred and the program exits; I set the return code on success as 0 (within SSIS) - specified on this website it should be. However, when executing the task in SSIS this fails; despite the log showing an exit code of 0 (see below!).
Changing this success code to 1 seems to give it all clear but was wondering if winSCP is returning the wrong code (presume not) or if the process task is returning a 1 as it has successfuly called winSCP?

Logs from SSIS;
SSIS package "TranferFile.dtsx" starting.
SSIS package "TranferFile.dtsx" finished: Success.
The program '[2464] TranferFile.dtsx: DTS' has exited with code 0 (0x0).

I noticed that the use of winSCP with SSIS isn't clearly documented either so if requested I'll pop together a quick guide on what to put where etc for those checking these boards. SSIS is a complicated mother! :P

Thanks,
John

I'm sorry, I'm confused :-(
So yo you know real exit code winscp.com is returning? Or do just guess it by result of the SSIS task?

Basically SSIS is setup to get the 0 success code back from winSCP; but this reports a failure when running. Instead when setting the expected return code to 1 it works; as does the file transfer so I know that's working fine and the winSCP logs shows this too.

I was just wondering if the latest build had crossed wires and now a successful transfer is returned as 1 and not 0 as explained in the documentation?

Exit codes are still the same.
Can you post your script and a log file?

No worries; doesn't matter now.

I believe winSCP is returning the codes but SSIS is simply detecting the opposite as it does succeed and as such marks the success as a 1 instead.

SSIS is the most awkward thing to use and config so I'll presume as usual it's that whcih is causing issues!

Thanks anyway!
Got another Q but will make a new post for that to keep it separate.

I am working on a similar thing. The package runs fine in the visual studio environment. I can also run the package from the sql server directly from the /msdb folder. But it does not work if I schedule it as a job or even execute it from a stored procedure. This one has me baffled in the lost city, any help is greatly appreciated. Thanks!

Anonymous wrote:

I am working on a similar thing. The package runs fine in the visual studio environment. I can also run the package from the sql server directly from the /msdb folder. But it does not work if I schedule it as a job or even execute it from a stored procedure. This one has me baffled in the lost city, any help is greatly appreciated. Thanks!

Please read FAQ. If that does not help, come back.

You could check the permissions, the configuration file for arguments and working directory, and location of the exe that ftp you files in another environment.

Also see guide to using WinSCP from SSIS.

How do you specify multiple parameters in the SSIS parameters line. The scipt parameter that I put in works "/scrip=path" but, when i try to add another parameter for logging it fails.

Yuriy wrote:

How do you specify multiple parameters in the SSIS parameters line. The scipt parameter that I put in works "/scrip=path" but, when i try to add another parameter for logging it fails.

/script=path /log=path
Maybe you've done:
"/script=path /log=path" ?

martin wrote:

Yuriy wrote:

How do you specify multiple parameters in the SSIS parameters line. The scipt parameter that I put in works "/scrip=path" but, when i try to add another parameter for logging it fails.

/script=path /log=path
Maybe you've done:
"/script=path /log=path" ?

I was specifying the full path to the script and the log file which are in the different directory from winscp executable. In the SSIS working directory parameter i specified the directory of executable and not the directory of where the script and log file are and I think it might have been the issue. I changed the working directory to where my script and log file are and in the parameters line i didn't have to use the whole path I just used /script=Script.txt and /log=log.txt and it worked. Thanks for pointing me in the right direction.

everytime that i run winscp from command line
if i check the errorlevel in cmd it always 0 even if the ftp is failed.

cmd>C:\Program Files\WinSCP\winscp.exe user:pass@server/hostkey="ssh-rsa 2048 xx:xx:xx...." /console /command "Option batch On" "option confirm off" "cd folderX" "Put D:\test_.txt" "close" "exit"
cmd>ECHO %ERRORLEVEL%
0

even if th efile is already exist in folderX i get an error in winscp window but it doesnt pass out

how can i get this error?

gabrieln wrote:

everytime that i run winscp from command line
if i check the errorlevel in cmd it always 0 even if the ftp is failed.

cmd>C:\Program Files\WinSCP\winscp.exe user:pass@server/hostkey="ssh-rsa 2048 xx:xx:xx...." /console /command "Option batch On" "option confirm off" "cd folderX" "Put D:\test_.txt" "close" "exit"
cmd>ECHO %ERRORLEVEL%
0

even if th efile is already exist in folderX i get an error in winscp window but it doesnt pass out

You need to use winscp.com. When running winscp.exe, the batch file does not wait until it finishes (it runs in parallel to the batch file).

thx for that, but still got a small issue
when i run
cmd>C:\Program Files\WinSCP\winscp.exe user:pass@server/hostkey="ssh-rsa 2048 xx:xx:xx...." /console /command "Option batch On" "option confirm off" "cd folderX" "Put D:\test_.txt" "close" "exit" & ECHO %ERRORLEVEL%
I get the errorlevel for the previouse running.
i.e. - if i run on the first time with the wrong password - i get

Searching for host...
Connecting to host...
Authenticating...
Using username "user".
Authenticating with pre-entered password.
Access denied.
Connection has been unexpectedly closed. Server sent command exit status 0.
Authentication log (see session log for details):
Using username "user".
Access denied.

Authentication failed.
batch on
confirm off
No session.
No session.
No session.
0

when i run it again i get

Searching for host...
Connecting to host...
Authenticating...
Using username "user".
Authenticating with pre-entered password.
Access denied.
Connection has been unexpectedly closed. Server sent command exit status 0.
Authentication log (see session log for details):
Using username "user".
Access denied.

Authentication failed.
batch on
confirm off
No session.
No session.
No session.
1

any idea for the delay?

Again, you run winscp.exe. You need to run winscp.com.

gabrieln wrote:

thx for that, but still got a small issue
when i run
cmd>C:\Program Files\WinSCP\winscp.exe user:pass@server/hostkey="ssh-rsa 2048 xx:xx:xx...." /console /command "Option batch On" "option confirm off" "cd folderX" "Put D:\test_.txt" "close" "exit" & ECHO %ERRORLEVEL%
I get the errorlevel for the previouse running.
i.e. - if i run on the first time with the wrong password - i get

Searching for host...
Connecting to host...
Authenticating...
Using username "user".
Authenticating with pre-entered password.
Access denied.
Connection has been unexpectedly closed. Server sent command exit status 0.
Authentication log (see session log for details):
Using username "user".
Access denied.

Authentication failed.
batch on
confirm off
No session.
No session.
No session.
0

when i run it again i get

Searching for host...
Connecting to host...
Authenticating...
Using username "user".
Authenticating with pre-entered password.
Access denied.
Connection has been unexpectedly closed. Server sent command exit status 0.
Authentication log (see session log for details):
Using username "user".
Access denied.

Authentication failed.
batch on
confirm off
No session.
No session.
No session.
1

any idea for the delay?

small cahange - i run winscp.com (and not winscp.exe)
Just to share - i found he problem, it is not with winscp but with windows, the errorlevel is not update if its in the same line with the command prompt

When I execute WinScp from my web application I get an exit code of 1 but there is no error messages in the log xml file and its doesnt throw any exceptions. It also does not bring back any directory listing in the log xml just the session details at the top. How would i find out what the actual error is.

Qwilliams wrote:

When I execute WinScp from my web application I get an exit code of 1 but there is no error messages in the log xml file and its doesnt throw any exceptions. It also does not bring back any directory listing in the log xml just the session details at the top. How would i find out what the actual error is.

You cannot see all errors in the XML log:
https://winscp.net/eng/docs/logging_xml#result
https://winscp.net/tracker/545
It's likely that the connecting failed. Use regular plain text log to troubleshoot the problem.

martin wrote:

Qwilliams wrote:

When I execute WinScp from my web application I get an exit code of 1 but there is no error messages in the log xml file and its doesnt throw any exceptions. It also does not bring back any directory listing in the log xml just the session details at the top. How would i find out what the actual error is.

You cannot see all errors in the XML log:
https://winscp.net/eng/docs/logging_xml#result
https://winscp.net/tracker/545
It's likely that the connecting failed. Use regular plain text log to troubleshoot the problem.

Is it possible to specify two log files?
In my use case I need the XML log for post processing, but would also like to have the extended text log for analyzing in case of an error (which occurs randomly).

Gerald wrote:

Is it possible to specify two log files?
In my use case I need the XML log for post processing, but would also like to have the extended text log for analyzing in case of an error (which occurs randomly).

I have just tried it - unfortunately only the first /log command line option is recognized.
Could you add this as a feature request, please?
Maybe as an enhancement of Bug 545 and Bug 649.

Thanks,
Gerald

Gerald wrote:

Could you add this as a feature request, please?

It was added already:
https://winscp.net/tracker/481

Thank you

Martin, you are awesome!