April 29, 2011 at 12:54 pm
Hi,
I'm having an issue with trying to figure out how to delete files in a folder location older than a certain number of days in this case lets say 10. I have looked far and wide online and most of the "solutions" that I find are hard to understand and do not give a complete picture of how to do it, they give about 80% and leave the 20% that are needed to make the solution work. If anyone can point me in the right direction of where to find a "complete" and working solution, either online or through the forums it would much appreciated. Thanks in advance.
April 29, 2011 at 3:01 pm
Here is a very similar conversation happening on the topic right now:
http://www.sqlservercentral.com/Forums/Topic1100808-149-1.aspx
There is a code sample for a Script Task being worked through at the moment that may be a good solution for you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 29, 2011 at 4:47 pm
You can use Script code available here: http://ssisctc.codeplex.com/wikipage?title=Script%20Task%3a%20File%20Properties&referringTitle=Home
Or the File Properties Task available here: http://filepropertiestask.codeplex.com/
Either of those inside a Foreach Loop with a File enumerator, a few variables, and a precedence constraint with an expression should do it.
Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components
May 2, 2011 at 6:41 am
Thanks for help Opc, once again you know what you are talking about. Oh yeah that package that I was working on before has taken on a life of its own, its in the 4th life cycle. Its pretty sweet, I just have to add a few more things to it, but as promised I will send it to you so that you can have something to use if you ever need it.
May 2, 2011 at 6:45 am
thanks for the help tmc, but I have a question about the link (dont know if you know the answer), but I was wondering in that code is there a way to add code to just look for how many days old the files are? because from what I can see its based on a date that is already declared. any further help from both you and opc would be appreciated
May 2, 2011 at 9:15 am
I wrote a .net utility that runs as a windows schedule task. You tell it what to doe via a config file which specifies the top level folders (wildcards and recursive) to search, the file name masks to match (or NOTmatch) and how many days to keep and/or encrypt/delete. It also has copy and move features somewhat like robocopy.
Hard to find handy utilities like this that are flexible and reliable so we made out own.
The probability of survival is inversely proportional to the angle of arrival.
May 2, 2011 at 11:44 am
I actually was able to figure this out by using powershell, I know its not the neatest way but the code is below:
$Now = Get-Date --grabs today's date
$Days = “30” --change this number based on how many days before today's date you want it to delete
$TargetFolder = “ ” --put your folder path or UNC path here [both types works]
$LastWrite = $Now.AddDays(-$days)
$Files = get-childitem $TargetFolder -include *.csv -recurse | Where {$_.LastWriteTime -le “$LastWrite”}
foreach ($File in $Files) --where it says *.csv you put there the type of file extension you want to delete from the folder path
{if (!($File -eq $null)) {remove-item $File | out-null} }
Hope the above code works. I'm still trying to figure out a way to do delete files using a script task in an ssis package but I keep on getting errors when using something like the code below:
Public Sub Main()
Dim SourcePath As String
Dim PurgeDays As Integer
PurgeDays = CInt(Dts.Variables("User::PurgeDays").Value)
SourcePath = CStr(Dts.Variables("User::SourcePath").Value)
For Each dir As DirectoryInfo In New DirectoryInfo(SourcePath).GetDirectories()
For Each file As FileInfo In dir.GetFiles()
If (Now - file.LastWriteTime).Days > PurgeDays Then file.Delete()
Next
Next
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
The error I keep getting is this "Reference to a non-shared member requires an object reference"
Any help you guys can throw my way would be greatly appreciated.
May 4, 2011 at 1:37 pm
I needed similar functionality, to remove files older than 14 days old. I wound up using the following, based on the DOS ForFiles command:
Forfiles -p E:\backups\test /m *.bak -d -14 /c "cmd /c del /q @path"
That's the sole line in a batch file run by windows task scheduler.
May 4, 2011 at 2:37 pm
Slick. Certainly solves the deleting old files problem in a very efficient manner.
The command shell is actually pretty powerful but I've always had a hard time finding a document anywhere that shows and explains all of the functionality in a clear and concise way ... and with examples of the often (hideously) cryptic syntax it uses.
The probability of survival is inversely proportional to the angle of arrival.
May 6, 2011 at 7:14 pm
Dave23 (5/4/2011)
I needed similar functionality, to remove files older than 14 days old. I wound up using the following, based on the DOS ForFiles command:Forfiles -p E:\backups\test /m *.bak -d -14 /c "cmd /c del /q @path"
That's the sole line in a batch file run by windows task scheduler.
Forfiles is definitely neat...but PowerShell is much cooler 😀
Here is how you would do the same thing using a one-line PowerShell script:
dir E:\backups\test\*.bak |? {$_.LastWriteTime -lt (get-date).AddDays(-14)} | del
It's actually three commands run through a single pipeline:
1. get the dir listing and pass results into the pipeline
2. query the pipeline for files with a LastWriteTime older than 14 days and pass the results through the pipeline
3. delete all the files in the pipeline
Credit to The PowerShell Guy for the boilerplate code.
Reference article http://technet.microsoft.com/en-us/library/ee176927.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply