Need a "system" for removing old files

[katbear]

This isn't a sql server question, per se, but it's definitely related to a sql server ssis project.

Basically, every day I download a bunch of files from an ftp site using ssis.

I want to keep 2 weeks worth of files. Sometimes I load the same files every day, for example:

8/3 download date modified
file20070803.txt 8/3
file20070802.txt 8/3
file20070801.txt 8/3

8/2 download
file20070802.txt 8/2
file20070801.txt 8/2
file20070731.txt 8/2

So the problem is, I can't rely on the file system timestamp to determine the age of the file, as the same files just get copied over, but with a different timestamp.

Just having a hard time figuring out the best way to handle this.

Thanks

 

[onpnt]

are you asking to do this from sql server?

if so DTS/SSIS can do it with a script (or CLR)

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[katbear]

Either from SSIS or a Windows scheduled task

 

[katbear]

I am more looking for a standard process to do this.

For example, copy all the files to a new folder, then maybe run a scheduled task every 2 weeks to delete the folder.

I guess maybe that would work.

I want to avoid completely having to check the date stamp on either the file, or in the file name itself, because I've got lots of different kinds of files to delete, they all have different naming conventions.

 

[mutley1]

Hi Katbear,

We use a date method on the filename to delete old backups. Please see code below - it's currently set to delete the filename that is 5 days older than today, so it would produce the cmd line of

del "D:\SQLData\Filename20070803*.txt

So if you have a tinker with this and run it nightly (or whatever fits your business needs, but don't forget it will only delete that file - if it doesn't run one day, then does the next, the file that would have been 5 days old would have been skipped and need manual deletion.

DECLARE @cmd varchar(255)
SET @cmd = 'del "D:\SQLData\Filename' + LEFT(REPLACE(REPLACE(CONVERT(VARCHAR,DATEADD(d,-5,GETDATE()), 112), ' ', '_'), ':', ''), 12) + '*.txt"'
print @cmd
--EXEC master..xp_cmdshell @cmd

Last line REMMED so you can test.

HTH.

M.

 

[mutley1]

Sorry - missed the bit about different naming conventions for the files - approx how many different formats / types will you expect?

M.

 

[katbear]

About 5 different naming conventions. That's why I want to avoid having to do any type of scripting.

I think my idea of downloading the files to "Folder 1" & "Folder 2" every morning, then delete Folder 1 every day, then delete Folder 2 every two weeks might work.

 

[mutley1]

But if you delete folder 2 every 2 weeks then you wont have the rolling 2 week retention - or does that not matter?

 

[katbear]

no, you're right i need the 2 week retention
at all times

it think what i might do instead is create folders for each day of the week as the backup location

then delete the folders after they reach a certain age

 

[mutley1]

Just have a structure like the below and a job to clear out the folder before copying:

MonWk1
MonWk2
TuesWk1
TuesWk2
etc.
etc.
FriWk1
FriWk2

and do the jobs to alternate between Wk1 and Wk2 - thats how we buck up our (non-SQL) document indexes.

HTH,

M.