August 14, 2009 at 8:38 am
Hello-
I'm attempting to put together foreach loop and file system task package to look in a specific directory and delete all files that are older then 3 days. I have no problems deleting all the files in the directory 😀 but I'm uncertain how to do this based on created on date.
Does anyone have any sample syntax to setup this expression?
Much appreciated!
August 14, 2009 at 11:36 am
I'd replace the File System task with a Script task and add the following code. Be sure to add the variable populated in your ForEachLoop Container's variable mappings tab to the ReadOnlyVariables section of the Script task. (in the code, this variable is named "FileName")
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim strPath As String = Dts.Variables("FileName").Value.ToString
If File.Exists(strPath) Then
Dim fi As FileInfo = New FileInfo(strPath)
If fi.CreationTime <= DateAdd(DateInterval.Day, -3, Today) Then
fi.Delete()
End If
fi = Nothing
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
August 14, 2009 at 11:43 am
Given that you are already able to delete the directory can we assume you've got a foreach file enumerator set up? If so then you just need to place another couple of steps into your loop.
1. A script task - using the file system object, get the age of the file. You probably already have a filepath global variable so you can pass that in. Use the result to get the creation date of the file.
2. Set up a precedence constraint with an expression.
Step 1 looks something like this...
Public Sub Main()
'Declarations
Dim sFilePath, sIntervalType As String
sFilePath = Dts.Variables("FilePath").Value.ToString
sIntervalType = Dts.Variables("IntervalType").Value.ToString
Dim oFile As New FileInfo(sFilePath)
Try
'Validate the passed in parameters
If (sIntervalType.ToUpper "MONTH") And (sIntervalType.ToUpper "DAY") Then
Dts.Events.FireError(0, "Problem determining file age", "The variable ""IntervalType"" must be set to ""Month"" or ""Day"".", "", 0)
Dts.TaskResult = Dts.Results.Failure
Return
End If
'Calculate age of the file (in months or days)
If sIntervalType.ToUpper = "MONTH" Then
Dts.Variables("ActualFileAge").Value = _
CType(DateDiff(DateInterval.Month, oFile.CreationTime, Now()), Int32)
Else
Dts.Variables("ActualFileAge").Value = _
CType(DateDiff(DateInterval.Day, oFile.CreationTime, Now()), Int32)
End If
'MsgBox("The file is " & Dts.Variables("ActualFileAge").Value.ToString & " " & sIntervalType.ToLower & "(s) old", MsgBoxStyle.OkOnly, sFilePath)
Catch ex As Exception
Dts.Events.FireError(0, "Problem determining file age", ex.Message, "", 0)
Dts.TaskResult = Dts.Results.Failure
Return
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Expression in Step 2 looks like this...
@ActualFileAge >= @DesiredFileAge
Where DesiredFileAge would be 3 in your case.
Doh... I was beaten to it!! Previous poster's script much quicker to develop.... But as a drag and drop junkie, I prefer mine 😉
Kindest Regards,
Frank Bazan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply