FTPing

[01310564]

Hello,

I have a DTS package that creates five files for export, each file has a diffrent name with todays date appended to the end. i.e. file1_01062005.txt. The problem occurs durring the ftp process. i thought i could use the FTP component in DTS but it only seams to download files not upload them. Next i though about creating a batch file but because this is an automated proccess and the file names keep changing how can i use a batch file to FTP the files to several people?

Can anyone help! It must be posible.

Cheers,

Hugh

 

[Catadmin]

Just a thought.

Store your file names in a table. Then use the xp_cmdshell command in a SQL script so that it picks the filenames from the table and then does your FTP command from the command prompt.

Here's some code that might help. You may have to adjust as I have only tested this with a delete command, not an FTP command.

Declare @Filename char(50),
	@TodayDate smalldatetime,
        @FileDate smalldatetime,
	@FTPfile sysname;

declare FileFTP CURSOR for select Filename, added from dbname.ownername.tablename where FTP is NULL

-- FTP is a datetime column where you store the date
-- once you've uploaded the file

set @TodayDate = GetDate();

Open FileFTP
fetch next from FileFTP into @FileName, @FileDate
while @@fetch_Status = 0
Begin
  If @FileDate < DateAdd(dd,-1,@TodayDate)
   Begin 
    Update dbname.ownername.tablename
    Set FTP = @TodayDate
    Where FileName = @FileName;
    set @FTPfile = 'FTP PUT ' + @FileName;
-- Correct the above set statement if needed
-- You may need multiple statements. One that opens FTP,
-- one that Puts the file and one that closes it.
    Exec Xp_cmdshell @FTPfile;
   END; 
 fetch next from FileFTP into @FileName, @FileDate;
END
Close FileFTP
Deallocate FileFTP

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

FTP has a command named MPUT with accepted wild card so you don't need to know the exact file name that you want to upload.
The commands to use (once logged into the FTP Server) are:
prom -- This stops ftp from prompting you for each file
mput file1*.txt -- This will upload any file that starts with file1 and ends with .txt.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[01310564]

Thanks that was a great help.

 

[mrdenny]

np

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[JayKusch]

I believe the FTP task works BOTH ways!

Thanks

J. Kusch

 

[mrdenny]

You need to use MGET to download. MPUT is for uploading only.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

Prom.

I knew there was something I was forgetting.

Thanks, Denny!!



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

no prob

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)