CODEWITHSUNDEEP
ssiswinscp
thotwielder
thotwielder

Reputation: 1707

How to fix exit code issue while using WinSCP for SFTP from within SSIS package?

I am using SSIS 2005 to do some SFTPtasks using WinSCP. I googled about the error but couldn't make it work. I got this error:

[Execute Process Task] Error: In Executing "C:\Program Files\WinSCP\WinSCP.exe" "-script=C:\Documents and Settings\nian_z\Desktop\temp\SSISMovingSOA\removeSOA8.txt" at "C:\Program Files\WinSCP", The process exit code was "1" while the expected was "0".

Here is my WinSCP script that I use:

option batch abort
option confirm off
#open sftp://user:password@server:22
#cd /m/vo/Cont/fileftp
get OrderOutbound*
close
exit

For above script, I even tried leaving only one statement at a time, but still got error.

Here is the setup of the execute process task.

Execute Process Task

Upvotes: 1

Views: 24585

Answers (3)

user16295274
user16295274

Reputation: 1

Earlier I was getting the same error but now is working fine for me.

SSIS Execute Process Task :

Executable - C:\SSIS\SSIS_2008_Projects\HRIS_RepomanFusion_Load\WinSCP\WinSCP.com

Arguments - "/script=wscpBatchTest.txt" /log=C:\SSIS\SSIS_2008_Projects\FTP_Load\WinSCP\WinSCP_log.txt

Working Directory - C:\SSIS\SSIS_2008_Projects\HRIS_RepomanFusion_Load\WinSCP

enter image description here

Here is the WinSCP script that I use: wscpBatchTest.txt

option batch
option confirm off
open sftp://username:[email protected]
option transfer binary
cd /home/SAFAA
get employeedetails.csv C:\SSIS\SSIS_2008_Projects\SAFAA\InputDirectory\
close
exit

Upvotes: -1

Arima
Arima

Reputation: 81

If you are running the script from SQL Server job agent then mostly you will get this exit error.

Scenario: In my case I faced when i m trying to download a file from sFTP server.

Solution :

Step 1 - Create a batch file @echo off

CD "C:\Program Files (x86)\WinSCP"

winscp.com /ini=nul /script=C:\path\download_script_pm.txt

Exit

Note: When calling using SQL Server Job agent make sure to NULL out the “ini” configuration else it will throw up this error

Step2 – create a txt file (winscp script)

option echo on

option batch on

option confirm off

open sftp://user:[email protected]/ -hostkey="ssh-edXXXX-xx-xx-xx-xx-xx-xx-xx-xx-xx-xx-xx-xx-xx-xx-xx-xx"

option confirm off

get -filemask=">=1D" /sftp_Path/File_Name_.csv C:\local_path_for_downloaded_file\"

exit

Note: make sure to use hostkey. To obtain host key you need to do the following : 1. Open winscp app tool and login to the server

  1. Click Session -> Generate Session / URL code option

  2. Check SSH Host key and copy the finger print value and use in the winscp hostkey value

Upvotes: 1

user756519
user756519

Reputation:

This is not an answer but an attempt to help you find a solution to your problem.

Try this:

Try the following steps to find out if your script is actually working outside of SSIS or not.

  • Click Windows Start and click Run...
  • Type cmd to open Command Prompt.
  • On the command prompt window, type the following command at the prompt to switch to the WinSCP installation directory. I have the WinSCP installed in the following directory. Change the path according to your environment settings.

cd "C:\Program Files (x86)\WinSCP"

  • If your WinSCP script file removeSOA8.txt is located in the path C:\Documents and Settings\userid\Desktop\temp\SSISMovingSOA\ with spaces, then type the following command by enclosing the script path in double quotes to run the script and also use the /log option to capture all the status messages.

WinSCP.exe "/script=C:\Documents and Settings\userid\Desktop\temp\SSISMovingSOA\removeSOA8.txt" /log=C:\temp\WinSCP_log.txt

After the script executes, your will find that a log file named WinSCP_log.txt will be created in the path C:\temp. Read through the file to identify if there are any error messages.

Attempt to run FTP in SSIS:

I tried downloading a file from FTP using WinSCP with the following script:

option batch abort
option confirm off
open ftp://myuserid:[email protected]:21 -passive=on
cd /root/somefolder/
option transfer binary
get SomeFileOnFTP.txt c:\temp\
close
exit

Here are the settings how I have configured the Execute Process Task within the SSIS package.

Execute Process Task

The process ran successfully in BIDS.

Execution

Hope that gives you an idea.

Upvotes: 5

Related Questions