Topic "Exit Code Issues in SSIS"

Author Message
JRB_BMB
[View user's profile]

Joined: 2008-07-01
Posts: 24
Location: Midlands, UK
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! Razz

Thanks,
John
Advertisements
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
I'm sorry, I'm confused Sad
So yo you know real exit code winscp.com is returning? Or do just guess it by result of the SSIS task?
_________________
Martin Prikryl
JRB_BMB
[View user's profile]

Joined: 2008-07-01
Posts: 24
Location: Midlands, UK
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?
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
Exit codes are still the same.
Can you post your script and a log file?
_________________
Martin Prikryl
JRB_BMB
[View user's profile]

Joined: 2008-07-01
Posts: 24
Location: Midlands, UK
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.
Guest




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!
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
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.
_________________
Martin Prikryl
isokolo

Guest


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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
Also see guide to using WinSCP from SSIS.
Yuriy

Guest


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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
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 Prikryl
Yuriy

Guest


prikryl 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.
gabrieln

Guest


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?
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
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).
_________________
Martin Prikryl
gabrieln

Guest


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
Quote:
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
Quote:
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?
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
Again, you run winscp.exe. You need to run winscp.com.
_________________
Martin Prikryl
gabrieln

Guest


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
Quote:
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
Quote:
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
Qwilliams

Guest


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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
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:
http://winscp.net/eng/docs/logging_xml#result
http://winscp.net/tracker/show_bug.cgi?id=545
It's likely that the connecting failed. Use regular plain text log to troubleshoot the problem.
Gerald

Guest


prikryl 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:
http://winscp.net/eng/docs/logging_xml#result
http://winscp.net/tracker/show_bug.cgi?id=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

Guest


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
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24555
Location: Prague, Czechia
Gerald wrote:
Could you add this as a feature request, please?

It was added already:
http://winscp.net/tracker/show_bug.cgi?id=481
_________________
Martin Prikryl
Gerald

Guest


Thank you
CaveBear3000

Guest


Martin, you are awesome!
Advertisements

You can post new topics in this forum






Search Site

What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!

Donate

About donations

$9   $19   $49   $99

About donations

Recommend

WinSCP Privacy Policy

WinSCP License