Solved

script to delete ftp files

Posted on 2014-09-04
4
351 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
4 Comments
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 185 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 185 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 130 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 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