Solved

SQL FTP Task Does not run consistently.

Posted on 2014-10-06
12
23 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:FNDAdmin
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:FNDAdmin
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

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…
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now