Exit Code Issues in SSIS

Advertisement

JRB_BMB
Joined:
Posts:
24
Location:
Midlands, UK

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

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
27,426
Location:
Prague, Czechia

Re: Exit Code Issues in SSIS

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?
_________________
Martin Prikryl

Reply with quote

JRB_BMB
Joined:
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?

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,426
Location:
Prague, Czechia

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

Reply with quote

JRB_BMB
Joined:
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.

Reply with quote

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!

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,426
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

Reply with quote

isokolo
Guest

ftp worked fine in VStudio and failed as a job.

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.

Reply with quote

Yuriy
Guest

SSIS Automation

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.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,426
Location:
Prague, Czechia

Re: SSIS Automation

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

Reply with quote

Yuriy
Guest

Re: SSIS Automation

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.

Reply with quote

gabrieln
Guest

error code in script doesn't work

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?

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,426
Location:
Prague, Czechia

Re: error code in script doesn't work

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

Reply with quote

gabrieln
Guest

error code in script doesn't work

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?

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,426
Location:
Prague, Czechia

Re: error code in script doesn't work

Again, you run winscp.exe. You need to run winscp.com.
_________________
Martin Prikryl

Reply with quote

gabrieln
Guest

Re: error code in script doesn't work

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

Reply with quote

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.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,426
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:
https://winscp.net/eng/docs/logging_xml#result
https://winscp.net/tracker/show_bug.cgi?id=545
It's likely that the connecting failed. Use regular plain text log to troubleshoot the problem.

Reply with quote

Gerald
Guest

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/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).

Reply with quote

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

Reply with quote

Advertisement

You can post new topics in this forum