asked on

SQL FTP Task Does not run consistently.

Hello,

I have a SQL job that uploads some TXT files to a remote sever nightly. The job will run once or twice after it has been manually run but then the FTP part of the SQL job will no longer function.

Windows Server 2008 R2 64Bit
MS SQL Server 2008 R2 64Bit
PSFTP.exe - 0.60.0.0

Here is the FTP step portion of the SQL Job:

--Send to Updated files to remote FTP Site
DECLARE @result int
DECLARE @file varchar(50)
DECLARE @arg varchar(100)
Select @file =' export.log'
select @arg = @file
DECLARE @cmd sysname, @var sysname
SET @var = @arg
SET @cmd = 'D:\MILLHTML\online~1\export\ftpcmdE.bat' + @var
print 'Out @cmd =' + @cmd
EXEC @result = master..xp_cmdshell @cmd
IF (@result = 0)
PRINT 'Success in Sending PCI Export file!'
ELSE
PRINT 'Failure in Sending PCI Export file!'

Here is the contents of the FTP .bat file:

d:
cd \MILLHTML\online~1\export
echo ********** Start Export ********** >> export.log
date /T >> export.log
time /T >> export.log
copy *.txt .\savefile\
rem rename *update*.txt FTPUpdate.txt
rem rename *inital*.txt FTPUpdate.txt
echo ********** Initiating SFTP Transfer ********** >> export.log
psftp -v -pw **password** **server*@ssh.*company**.com -b sshtransfer.ftp >>export.log
date /T >> export.log
time /T >> export.log
echo ********** SFTP Transfer Complete ********** >> export.log
erase *.txt
echo ********** End Export ********** >> export.log
date /T >> export.log
time /T >> export.log

I have tried configuring the SQL job with a domain account with Admin privs as well as the SQL SA account. SQL Job logs show all successful history for this job. Windows Server Event Log does not show any errors.

I would sincerely appreciate any and all assistance with this problem.

DcpKing

I can't comment on using PSFTP, but it sounds like your FTP part may be the problem. I have used WinSCP with success for the last 18 months for a lot of daily FTP tasks, including a daily 6 GB download, so can attest to its reliability. It can be run from the command line, so you can use SSIS to run a batch job controlling WinSCP.

hth

Mike

FNDAdmin

ASKER

Thanks Mike,

I think you are right. PSFTP is pretty old and I actually after posting the initial message redesigned the program to us WinSCP. Which worked great in testing, but failed when it ran as a scheduled task. I'll post the FTP log here just to see if you can pinpoint the issue, but I may create another thread for it.

FNDAdmin

ASKER

The BAT file that runs the job:

d:
cd \MILLHTML\online~1\export
echo ********** Start Export ********** >> export.log
date /T >> export.log
time /T >> export.log
copy *.txt .\savefile\
rem rename *update*.txt 123Update.txt
rem rename *inital*.txt 123Update.txt
echo ********** Initiating SFTP Transfer ********** >> export.log
psftp -v -pw <<password>> <<user>>@ssh.<<host>>.com -b sshtransfer.ftp >>export.log
date /T >> export.log
time /T >> export.log
echo ********** SFTP Transfer Complete ********** >> export.log
erase *.txt
echo ********** End Export ********** >> export.log
date /T >> export.log
time /T >> export.log

The sshtransfer.ftp command:
mput *.txt
quit

The Log:
********** Start Export **********
Tue 10/07/2014
05:30 PM
********** Initiating SFTP Transfer **********
. 2014-10-07 17:30:09.284 --------------------------------------------------------------------------
. 2014-10-07 17:30:09.284 WinSCP Version 5.5.5 (Build 4605) (OS 6.1.7601 Service Pack 1 - Windows Server 2008 R2 Standard)
. 2014-10-07 17:30:09.284 Configuration: D:\MILLHTML\ONLINE~1\export\WinSCP.ini
. 2014-10-07 17:30:09.284 Local account: FND\SQLUser
. 2014-10-07 17:30:09.284 Working directory: D:\MILLHTML\ONLINE~1\export
. 2014-10-07 17:30:09.284 Process ID: 2696
. 2014-10-07 17:30:09.284 Command-line: "D:\MILLHTML\ONLINE~1\export\WinSCP.exe" /console=555 /consoleinstance=_4884_670 "/console" "/script=WinSCP.ftp" "/log=WinSCPexportPCI.log"
. 2014-10-07 17:30:09.284 Time zone: Current: GMT-4, Standard: GMT-5 (Eastern Standard Time), DST: GMT-4 (Eastern Daylight Time), DST Start: 3/9/2014, DST End: 11/2/2014
. 2014-10-07 17:30:09.284 Login time: Tuesday, October 07, 2014 5:30:09 PM
. 2014-10-07 17:30:09.284 --------------------------------------------------------------------------
. 2014-10-07 17:30:09.284 Script: Retrospectively logging previous script records:
> 2014-10-07 17:30:09.284 Script: option batch abort
< 2014-10-07 17:30:09.284 Script: batch abort
> 2014-10-07 17:30:09.284 Script: option confirm off
< 2014-10-07 17:30:09.284 Script: confirm off
> 2014-10-07 17:30:09.284 Script: option transfer binary
< 2014-10-07 17:30:09.284 Script: transfer binary
> 2014-10-07 17:30:09.284 Script: open <<user>>:***@ssh.<<host>>.com:22
. 2014-10-07 17:30:09.284 --------------------------------------------------------------------------
. 2014-10-07 17:30:09.284 Session name: <<user>>@ssh.<<host>>.com (Ad-Hoc site)
. 2014-10-07 17:30:09.284 Host name: ssh.<<host>>.com (Port: 22)
. 2014-10-07 17:30:09.284 User name: <<user>> (Password: Yes, Key file: No)
. 2014-10-07 17:30:09.284 Tunnel: No
. 2014-10-07 17:30:09.284 Transfer Protocol: SFTP (SCP)
. 2014-10-07 17:30:09.284 Ping type: -, Ping interval: 30 sec; Timeout: 15 sec
. 2014-10-07 17:30:09.284 Proxy: none
. 2014-10-07 17:30:09.284 Send buffer: 262144
. 2014-10-07 17:30:09.284 SSH protocol version: 2; Compression: No
. 2014-10-07 17:30:09.284 Bypass authentication: No
. 2014-10-07 17:30:09.284 Try agent: Yes; Agent forwarding: No; TIS/CryptoCard: No; KI: Yes; GSSAPI: No
. 2014-10-07 17:30:09.284 Ciphers: aes,blowfish,3des,WARN,arcfour,des; Ssh2DES: No
. 2014-10-07 17:30:09.284 SSH Bugs: A,A,A,A,A,A,A,A,A,A
. 2014-10-07 17:30:09.284 Simple channel: Yes
. 2014-10-07 17:30:09.284 Return code variable: Autodetect; Lookup user groups: A
. 2014-10-07 17:30:09.284 Shell: default
. 2014-10-07 17:30:09.284 EOL: 0, UTF: 2
. 2014-10-07 17:30:09.284 Clear aliases: Yes, Unset nat.vars: Yes, Resolve symlinks: Yes
. 2014-10-07 17:30:09.284 LS: ls -la, Ign LS warn: Yes, Scp1 Comp: No
. 2014-10-07 17:30:09.284 SFTP Bugs: A,A
. 2014-10-07 17:30:09.284 SFTP Server: default
. 2014-10-07 17:30:09.284 Local directory: default, Remote directory: home, Update: Yes, Cache: Yes
. 2014-10-07 17:30:09.284 Cache directory changes: Yes, Permanent: Yes
. 2014-10-07 17:30:09.284 DST mode: 1; Timezone offset: 0h 0m
. 2014-10-07 17:30:09.284 --------------------------------------------------------------------------
. 2014-10-07 17:30:09.284 Looking up host "ssh.<<host>>.com"
Tue 10/07/2014
05:30 PM
********** SFTP Transfer Complete **********
********** End Export **********
Tue 10/07/2014
05:30 PM

FNDAdmin

ASKER

After the Looking up host "ssh.<<host>>.com" is should then connect. I don't know why is doesn't connect and continue or at least provide me with an error message, something..

DcpKing

So what happened after " Looking up host "ssh.<<host>>.com" " ? Did the job stop or hang, or what?

If you got an error message of some sort (even after 30 secs or more) try checking it out on their support page.

hth

Mike

FNDAdmin

ASKER

Exactly, I have no clue what happened after "Looking up host "ssh.<<host>>.com". The log does not tell me. The job runs at night, so no one is around to see it kick off.. I'll check out their support page.. Thanks!

DcpKing

Try running the download part of the job during the day so you're there to see it fail. Also check out the Windows event log on the machine(s) your job's running on. Maybe some sort of error message can be found there

Mike

FNDAdmin

ASKER

I watched it kick off last night @ 5PM. Nothing popped up on the server's desktop. No Windows Server event log messages either. When I had to run the program manually it does pause for a second @ "Looking up host "ssh.<<host>>.com", then continues on with "Connecting to xxx.xxx.xxx.xxx port 22"...

DcpKing

When you do it manually you're running it as yourself; when from the scheduled job as the SQL Agent. Try setting yourself as the person represented in a Credential (under Security for the server in SSMS), and then run the job using _that_ person instead of SQLServer_Agent.

hth

Mike

ASKER CERTIFIED SOLUTION

FNDAdmin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FNDAdmin

ASKER

It seems that my issue was resolved by editing the compatibility of the WinSCP.exe and enabling the 'Run this program as an administrator' setting.