Solved

SQL FTP Task Does not run consistently.

Posted on 2014-10-06
12
61 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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