December 26, 2001 at 4:55 pm
I need to use DTS to scan a directory at an FTP site and retrieve all .txt files. I'd also like to change the extension on the files after they are retrieved. Any clues would be much appreciated.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
December 27, 2001 at 6:11 am
The http://ftp.exe supports the rename command. If you're doing it locally, you can either shell to the OS and simply do a 'rename', or you can use the FileSystemObject and process the file that way.
Andy
December 27, 2001 at 3:07 pm
I am a rookie at VBScript. Could you please show me how to shell out the http://FTP.exe?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
December 27, 2001 at 6:27 pm
You could also write a batch file to do the FTP tasks and run the batch file from within a DTS using the execute process task(I think this is the correct type of task). Or you could use the FTP task to put the files in the directory you want with a different filename
December 28, 2001 at 7:21 am
I like jwiners solution in that the batch file ftp process is probably the way to go. I would then use a xp_cmdshell t-sql solution to rename the files. Example might be;
declare @sqlstring varchar(250)
select @sqlstring = ('exec xp_cmdshell "rename \\YourServer\YourPath\YourFile YourFile_'+convert(varchar(32), getdate(), 112)+'"')
exec (@sqlstring)
I used the @sqlstring solution becuase I couldn't get the addition of the date to the filename to fly without it. If you can I would like to see that solution as well.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 28, 2001 at 11:01 am
I also like the batch process option. The mget command within FTP seems to get the job done.
I'd like to rename the .TXT files on the FTP server. The server owner has agreed to grant rights to do that. How would I configure David B's solution to do that?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
December 28, 2001 at 11:10 am
You can look at my article on PushFTP. This could be easily altered to do renames instead, however, you need to know the file names. If you do not know these, you would have to do a dir or ls and then parse the results.
Steve Jones
January 2, 2002 at 8:51 am
Here is the code I use to get files from an FTP server:
SET GLOBALERRORLEVEL=0
net use \\0.0.0.0\ipc$ /delete
IF NOT %ERRORLEVEL%==0 SET ERRORLEVEL=0
net use \\0.0.0.0\ipc$ password /user:computername\username
COPY "\\0.0.0.0\SourceFolderName\filename.txt" "\\username\DestinationFolderName"
IF NOT %ERRORLEVEL%==0 SET GLOBALERRORLEVEL=1
net use \\0.0.0.0\ipc$ /delete
IF NOT %ERRORLEVEL%==0 SET ERRORLEVEL=0
IF NOT %GLOBALERRORLEVEL%==0 SET ERRORLEVEL=1
--end of batch file
0.0.0.0 is the IP address of the FTP server.
For this to work you must have a user name and password on the source and destination computers. You should be able to put a rename command in this batch file and the file name can be *.txt to copy all text files.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 2, 2002 at 10:17 am
Interesting. Does this work in all windows versions, or is it a 2000 thing. I wasn't aware NET USE could run through FTP, especially in Pre-W2K systems.
Steve Jones
January 2, 2002 at 10:35 am
I have only used this with SQL Server 7.0 running on Windows NT 4.0. We are preparing to switch to Windows 2000 but haven't got that far yet.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 2, 2002 at 11:30 am
It would be interesting to see if this works with a Unix based server. trying to find one now.
Steve Jones
January 2, 2002 at 12:48 pm
Unless I'm mistaken, the script given uses RPC only. There isn't anything there for FTP. I think you're just happening to hit a share on the NT 4 server.
The net use ipc$ allows for validation against the server and then you're using a straight copy... not a get or mget (for multiple files), and copy is not an FTP command.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 2, 2002 at 1:22 pm
Correct, I am using a shared directory on our FTP server. Forgive me for not realizing that would be an important fact, however access to the share is limited to SQL Server and to the provider who places the data on our FTP server.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply