Solved

script to delete ftp files

Posted on 2014-09-04
4
332 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 47

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 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