July 20, 2004 at 3:27 am
I have created a job to move various backup files around our network. The job has a number of steps to it including one which is a transact SQL Script which looks something like this:
exec LinkedServer.master.dbo.xp_cmdshell 'ftp -s:c:\PutFiles.txt'
PutFiles.txt contains the following:
open 111.11.111.11
Login
Password
bin
lcd F:\Backupdir
mput *.zip
y
y
y
y
y
quit
Following this step is a step which unzips the files.
There are five very large zip files in the relevant directory and the FTP step starts to run as expected. However, after about 30 minutes the step finishes and SQL Agent starts the next step, that of unzipping the files, even though not all of the files have yet been FTP'd. In fact, at the time that the unzip step starts the FTP job has only transferred three and a half of the files. The FTP eventually completes and the four files are unzipped but the final file (which wasn't in the directory when the unzip job started) is not.
I'd be really grateful for any insights into what is happening and any suggested solutions to this problem.
Thanks!
July 20, 2004 at 4:01 am
Can you build in a step that does a "sleep" step or modify the unzip step to not start until all 4 files are there?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 20, 2004 at 4:09 am
I probably could but I would like to understand why this is happening.
Sean
July 21, 2004 at 7:04 am
Do you have to use the FTP process to move the files around. If it's your own network, can't use move the files using the File Scripting Object?
July 21, 2004 at 7:35 am
Thanks for the suggestion but I'm not sure how the File Scripting Object would work. Would it involve doing a Windows copy? If so, it will not be quick enough for our purposes. The files are very large (approx 50GB) and need to be moved to Toronto from London within a tight time limit.
As it happens, I was wrong about only four files being unzipped. Surprisingly, all of the files are unzipped, even the ones that are not in the directory when the Unzip step begins so the job actually achieves its purpose. I am reluctant to push the process to a production environment though without understanding what is going on.
July 21, 2004 at 4:40 pm
In PutFiles.txt, you have:
open 111.11.111.11
Login
Password
bin
lcd F:\Backupdir
mput *.zip
y
y
y
y
y
quit
You could rewrite it as follows:
open 111.11.111.11
Login
Password
bin
lcd F:\Backupdir
prompt
mput *.zip
quit
The prompt keyword will switch to non interactive mode, saving you from replying to each put statement.
Alternatively, you can issue
ftp -i -s:c:\PutFiles.txt
(the -i switches off interactive mode) and contents of PutFiles.txt would then be
open 111.11.111.11
Login
Password
bin
lcd F:\Backupdir
mput *.zip
quit
This may help as there are no prompts being issued and responded to.
In regards to being certain that all files have been downloaded prior to unzipping, you may wish to have a check step (a directory listing of the destination directory should do the trick) or alternatively, after the mput do a put checkfile.txt (where checkfile.txt is a tiny text file, content can be anything). So prior to unzipping, you check that checkfile.txt is present, if yes, continue with unzip and then delete checkfile.txt from destination.
Though, personally, I would go with option 1 as I would be 100% certain - it is a little bit more coding, but as you said, you have a tight schedule and certainty is crucial.
Hope this helps!
Serdal
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply