Solved

script to delete ftp files

Posted on 2014-09-04
4
374 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 50

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

707 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