Solved

script to delete ftp files

Posted on 2014-09-04
4
339 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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 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