Rename a file on a remote server

Question

Rename a file on a remote server

Kathy-268569

SSC Veteran

Points: 292
More actions
August 6, 2007 at 8:27 am

#174767

Hi
I have a DTS package which ftps a file from a remote server to my PC. That all works fine. Once the ftp is done I need to connect to the remote server and rename the file I have just ftpd.
I can do this from the command line on my PC using ren etc., but if I put this into an xp_cmdshell I get a successful execution but the file is not actually renamed.
Essentially I am trying to replicate the following bit of unix script via DTS (obviously servername, username etc aren't the real ones)
ftp -in <<EOF
open remote_box
user username password
get NewFile.txt
mdelete OLDNewFile.txt
rename NewFile.txt OLDNewFile.txt
bye
EOF
Any suggestions?

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You should be able to put all that in a script that you run using ftp to rename the file. We did that many times at my previous employer's. Unfortunately, I can't remember the exact format of the script files or the ftp command line. What I can tell you is we did this on both Windows servers (with MS command line ftp) and Solaris servers (unix command line), and they were very similiar in format.

Kathy-268569 SSC Veteran Points: 292 More actions · Answer 2

I was hoping that there was a solution that didn't involve writing a batch file, but yes, if all else fails I'll be going down that route. It's frustrating as I have the ftp working beautifully in DTS, but can't figure out the rest of it.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

FTP in DTS is pull only. Once you move to SQL2K5 and SSIS, that changes.

philcart SSC-Forever Points: 47795 More actions · Answer 4

Kathy, are you using the standard FTP task that comes with DTS? I remember that ww.sqldts.com built an add-on task for FTP, can't remember it's full functionality, might be worth a look.

For the FTP script, this page will give you an idea of what the script command should be like,

http://www.computerhope.com/software/ftp.htm

--------------------
Colt 45 - the original point and click interface

Kathy-268569 SSC Veteran Points: 292 More actions · Answer 5

Yes I'm just using the standard DTS task with a dynamic properties task to sort out the file name. I'll investigate the add-on, thanks

Kathy-268569 SSC Veteran Points: 292 More actions · Answer 6

Hi - had a play with the add on but can't really progress any further. It makes it easier to suffix the date to the filename etc. but can't help me with the rename on the remote server.

Think I'm going to have to resort to calling a batch file which is a shame. I wanted a nice elegant solution that my co-worker could copy and tweak to set up further jobs....

Johan Andersson-475441 Grasshopper Points: 11 More actions · Answer 7

Is it possible to use the SQL Server 2005 SSIS - FTP task to rename a file on remote FTP-server?