Secure FTP Batch File Problem

Question

Secure FTP Batch File Problem

darrenkelly

SSCommitted

Points: 1624
More actions
February 13, 2007 at 2:55 am

#118929
Hi All
I have been instructed to remove our FTP batch files from our SQL Server and recode then using Secure http://FTP.  For this task I used the program Putty, SFTP.
I have created a Batch file called DownloadOracleFiles.bat (which is used to download files from our Oracle Server). It creates a log file and out puts data to the log file and makes a secure connection to the Oracle Server, and calls an FTP file DownloadOracleFilesScr.scr, which contains the mget commands. COntents of files are below.
My environment is as follows:-
1. The SQL Server 2000 SP4
2. Windows 2003 Server
My problem is as follows:-
- Doubling clicking the Batch file on the Server all files are downloaded and writes are made to the log file
- Placing the batch file in a DTS package and executing the DTS package, all files are downloaded and writes are made to the log file.
- Placing the DTS package as a scheudle Job, NO Files downloaded writes made to the log file
- Placing the Batch file as a step in a scheudle Job, NO Files downloaded, writes made to the log file
I need to get this to work as a scheudled job but have no idea why its failing as one.
Can any one shed any light on what may be going wrong?
Any help or assistance or would be greatly appreciated.
Thanks in advance.
Darren
Contents of Batch file: DownloadOracleFiles.bat
SET FTPLoc=F:\ScriptedFiles\FTPScripts
SET OracleDownLoadedFilesLocation=F:\ScriptedFiles\Oracle\DownloadedFiles
SET FTPLogFile = FTPLog.log
CD %OracleDownLoadedFilesLocation%
ECHO ************         >> %FTPLoc%\%FTPLogFile.log
ECHO * Starting FTP        * >> %FTPLoc%\%FTPLogFile.log
ECHO * Date is: %date%  * >> %FTPLoc%\%FTPLogFile.log
ECHO * Time is: %time%  * >> %FTPLoc%\%FTPLogFile.log
ECHO **************** >> %FTPLoc%\%FTPLogFile.log
%FTPLoc%\psftp.exe username@200.20.2.20 -pw password -batch -b %FTPLoc%\DownloadOracleFilesScr.scr
ECHO ************         >> %FTPLoc%\%FTPLogFile.log
ECHO * End FTP              * >> %FTPLoc%\%FTPLogFile.log
ECHO * Date is: %date%  * >> %FTPLoc%\%FTPLogFile.log
ECHO * Time is: %time%  * >> %FTPLoc%\%FTPLogFile.log
ECHO **************** >> %FTPLoc%\%FTPLogFile.log
Contents of Batch file: DownloadOracleFilesScr.scr
mget CKB*
mget TI*
mget ORP*
mget OIP*
mget OMM*

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i'm guessing that the username that is used to run SQLJobs doesn't have rights to the F:\ScriptedFiles\Oracle\DownloadedFiles folder;

is this a mapped drive? try changing it to a uncpath: ie \\servername\ScriptedFiles\Oracle\DownloadedFiles folder;

when you run the job as a bat file, you are running it as your network login, which obviously can access the folder, but SQL is often started using the System account, which never logs into the network..

you might want to change the account that is used to run SQL to fix this:

Enterprise Manger...right click>>properties>>Security Tab>>Start Service Account section at the bottom

HTH

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

darrenkelly SSCommitted Points: 1624 More actions · Answer 2

Lowell, thanks for taking time to reply. I appreciate it.

The User who runs both SQL Server and SQL Agent is a dedicated SQL Account with Admin privileges on the Server.

The F: drive is a local drive on the machine

The account that I log onto the machine is the User account that I use to run SQL Server and SQL Agent.

So on that information it would seem that for now at least, I have the basics covered in relation to account privileges and the SQL Server Service Account User having access to the Download folder in question.

Lowell SSC Guru Points: 323518 More actions · Answer 3

Man you've already covered the basics, I see;

Ok, i'd look at file attributes , file locking, and finally folder security... gotta cover the basics.

any chance that the folder or the files in F:\ScriptedFiles\Oracle\DownloadedFiles are readonly?

any chance that the files are currently open by another process/texteditor or anything?

right click Properties>>security for the folder and confirm that the specific account has full access?

Check to see who created the folder... maybe it's exclusive to an admin or admin group, and not open to the account in question.

my last idea is that the mget process itself may be occuring really really quickly, and the disk write behind cache (or whatever it is called) hasn't caught up yet, and the process itself still technically has a lock on the file(s)

After this, I'm running low on ideas..sorry

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

darrenkelly SSCommitted Points: 1624 More actions · Answer 4

Again Lowell, many thanks for taking time to reply.

I checked the properties on folder and all Server Administators have access to it, and obviously at some other point I made sure the Admin account in question had full access to it too.

You were 100% right about the folder been readonly, which I have changed but alas to no avail.

Taking into account what you said about the mget happening very quickly I changed it to just acquire 1 single file, but again to no avail. No other resources or processes would be using the file. Interesting comment about the disk, I will liase with the network lads in relation to that.

Below is the log of the step after it is run, but I am not finding it of any help. Maybe you may see something there that I am ignoring. Other than the C:\Program Files\Outlook Express which I am gathering is something to do with SQL Agent, but thats only a guess!!!

Executed as user: ENTIRL\sqladmin. ...\ScriptedFiles\FTPScripts C:\Program Files\Outlook Express>SET OracleDownLoadedFilesLocation=F:\ScriptedFiles\Oracle\DownloadedFiles C:\Program Files\Outlook Express>SET FTPLogFile = DownloadOracleFilesFTPLog.log C:\Program Files\Outlook Express>CD F:\ScriptedFiles\Oracle\DownloadedFiles C:\Program Files\Outlook Express>ECHO *************************************************************** 1>>F:\ScriptedFiles\FTPScripts\DownloadOracleFilesFTPLog.log C:\Program Files\Outlook Express>ECHO * Starting to FTP Files down from ORACLE * 1>>F:\ScriptedFiles\FTPScripts\DownloadOracleFilesFTPLog.log C:\Program Files\Outlook Express>ECHO * The date is: 14/02/2007 The time is: 10:08:53.81 * 1>>F:\ScriptedFiles\FTPScripts\DownloadOracleFilesFTPLog.log C:\Program Files\Outlook Express>ECHO *************************************************************** 1>>F:\ScriptedFil... Process Exit Code 0. The step succeeded.

Ron Aughenbaugh SSC Eights! Points: 975 More actions · Answer 5

To start, I have not used PSFTP..But here are my steps for troubleshooting.

Start by adding @ECHO OFF to the top of the batch script so you do not see everything the BAT script is doing. Do add the -bc and the -be switch to the PSFTP line. The -bc switch so you can see in the output what your scr script is doing. and the -be switch to continue on errors. Hoefully this will give you a better lock on where it is failing.

darrenkelly SSCommitted Points: 1624 More actions · Answer 6

Ron, your assitance put me on the right track.

After putting in the switches I was able to confirm that by all accounts the files were been downloaded.

I did a serach on the Server and low and behold I found the files in the following location

C:\Program Files\Outlook Express

This registerted with me as I saw this location in the Error Log previously. So all I ended up doing to completely fix the problem was add in another DOS command as it looked as if the Batch file was not going to the

CD %OracleDownLoadedFilesLocation%