Solved

SQL FTP Task Does not run consistently.

Posted on 2014-10-06
12
31 Views
Last Modified: 2016-06-14
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.
0
Comment
Question by:FNDAdmin
  • 7
  • 4
12 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 40367583
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
0
 

Author Comment

by:FNDAdmin
ID: 40368317
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.
0
 

Author Comment

by:FNDAdmin
ID: 40368325
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
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:FNDAdmin
ID: 40368329
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..
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40369214
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
0
 

Author Comment

by:FNDAdmin
ID: 40369269
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!
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40369482
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
0
 

Author Comment

by:FNDAdmin
ID: 40370491
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"...
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40372227
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
0
 

Accepted Solution

by:
FNDAdmin earned 0 total points
ID: 40372830
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.
0
 

Author Comment

by:FNDAdmin
ID: 40373058
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.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tempdb log contention 16 40
MSSQL Speen Degradation 4 23
SQL Server 2012 r2 - calculations/operation on many Temp Tables 6 22
Webservices in T-SQL 3 31
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question