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.
FNDAdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DcpKingCommented:
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
FNDAdminAuthor Commented:
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.
FNDAdminAuthor Commented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

FNDAdminAuthor Commented:
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..
DcpKingCommented:
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
FNDAdminAuthor Commented:
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!
DcpKingCommented:
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
FNDAdminAuthor Commented:
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"...
DcpKingCommented:
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
FNDAdminAuthor Commented:
Hi Mike,

   When I do a 'Run as' and enter in the creds for SQLUser it runs fine. I may have made some headway with setting the security on WinSCP.exe to 'Run as Administrator'. It seemed to work last night. I will test it today.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FNDAdminAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.