-2

I have a batch file that gets executed via the command prompt (CMD), however the same does not execute if called within a SQL job step. The batch job is opening a WINSCP session and transferring a text file from a local directory onto the client directory. The SQL job does not fail, it executes completely, however the text file is not copied. The batch file calls winscp.com as follows

cd C:\Program Files (x86)\WinSCP\winscp.com /script=C:\Test\FTPUpload.txt

and FTPUpload.txt contains the following:

option batch continue
option confirm off
open ftp://user:password@something@somthing.com -explicit 
lcd C:/TestUpload/ 
cd /TestClient 
put -delete -nopermissions -resumesupport=off *.txt 
close 
exit .

I have given SQL Agent account full permissions on WINSCP and also on the batch files. SQL Agent is also under the local admin. SQL Server version is SQL Server 2008 R2. At the SQL job level I have used the Type: Operating System (CmdExec).

been rattling my brains for quite some time, any help would be appreciated :)

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
RAnand
  • 29
  • 1
  • 11
  • It looks like you are not executing it, but havin a syntactically incorrect line in there: `cd C:\Program Files (x86)\WinSCP\winscp.com /script=C:\Test\FTPUpload.txt` with that line you are not achieving anything. It is a mix of changing the directory to the `winscp`dir and using it at the same time... And please [edit] your question according to the [formatting-help](http://stackoverflow.com/editing-help). – geisterfurz007 Jan 19 '17 at 13:46
  • not sure what you mean by not achieving anything. It works perfectly if i execute the batch file in tihe windows command prompt. it calls the winscp, opens the session, transfers the file and closes it. I can see the text file on the destination directory. Is there something different if the cmd file is called via the SQL job agent? – RAnand Jan 19 '17 at 14:06
  • Then please edit the line correctly. As it looks now, it does really seem odd. – geisterfurz007 Jan 19 '17 at 14:09

1 Answers1

-1

Made changes to the batch file and the winscp commands

Batch file changes winscp.com /console /script="C:\CMS\FTPUpload.txt"

in the text file i added the hostkey parameter

option batch continue
option confirm off
open ftp://user:password@something@somthing.com -explicit -hostkey=*
lcd C:/TestUpload/
cd /TestClient
put -delete -nopermissions -resumesupport=off *.txt
close
exit .

RAnand
  • 29
  • 1
  • 11