?
Solved

script to delete ftp files

Posted on 2014-09-04
4
Medium Priority
?
395 Views
Last Modified: 2014-09-08
Here is my situation

I have a vendor ftp site I access that has a list of files at the root along with a folder named archive

I have a process that copies all the files to the archive folder


I would like to execute a file (via a stored proc) that will delete the files in the archive folder that have a modification date older than 7 days I thought that  using

SELECT  @cmd = 'ftp -s:' + @workdir + '\' + @workfilename

EXEC master..xp_cmdshell @cmd

I thought I found an example that would work if the file contained the following:

$current_date_time=GETDATE(FORMAT0)
PRINT("Current date and time is ".$current_date_time)

$a_week_ago=$current_date_time-(7*24*60*60)
PRINT("A week ago was ".$a_week_ago.". Every file older than this will be deleted from the FTP server")

# Retrieve the remote file list
cd archive

# For each file in the current remote directory
# check if it is older than a week.
# If so delete it
FOREACH $remote_file IN $remote_file_list
    $file_last_modification_time=GETFILETIME(REMOTE,$remote_file)
    IF($file_last_modification_time<$a_week_ago)
        DELETEFILE($remote_file)
    END IF
END FOREACH

Unfortunately I get invalid command errors

Not sure how to proceed
0
Comment
Question by:johnnyg123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 740 total points
ID: 40305537
Just two recommendations:

1.

It's a good practice to not enable the xp_cmdshell since there's securities issues with that stored procedures. It allows access to your operating system.

2.

Let SQL Server to do tasks on databases and let Operating System to do tasks on files.
With this I want to suggest you to do this with a vb.script, powershell or other tool that's not SQL Server. Anyway the code you posted is not for SQL Server.
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 740 total points
ID: 40305904
Why not run a powershell script via SQL Agent? I suspect by "via a stored procedure", you mean you want to control the schedule via SQL Agent (which I use for scheduling all kinds of things instead of the Windows Scheduler) - you can choose to execute a powershell script as one of the options, so that would meet your needs. If you really need the results of a SQL query to see which files to act on (rather that listing them directly off the FTP site), you can use T-SQL to dump query results to a CSV file that your powershell script then consumes and acts on.

Please let me know if I'm reading the problem incorrectly.
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 520 total points
ID: 40306092
What's your FTP client program? If it's something like WinSCP then it's easy to program on the command line, so you can write a batch file to run WinSCP with the appropriate commands. Then schedule an SSIS job to run the batch file every week or whenever ...

hth

Mike
0
 

Author Closing Comment

by:johnnyg123
ID: 40310500
Thanks for all the posts!

I think I'm going to use a solution that incorporates all the suggestions
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question