Take the oldest file in foreach loop

Question

I have multiple file in a folder with a naming convention

Name_MoreName_DDMMYYYY_SomeNumber_HHMMSS.txt

How can I take only one file in a foreach loop which has oldest Date and Time (i.e. oldest DDMMYYYY and HHMMSS). I am restricted to use the foreach loop.

Ex:

• Name_MoreName_22012012_SomeNumber_072334.txt
• Name_MoreName_22012012_SomeNumber_072134.txt
• Name_MoreName_24012012_SomeNumber_072339.txt
• Name_MoreName_22012012_SomeNumber_072135.txt

So the oldest file will be

Name_MoreName_22012012_SomeNumber_072134.txt

how can i take the oldest file only ?

Answer 1

Date Expression:

SUBSTRING( @[User::Filename], FINDSTRING( @[User::Filename],"_" , 2) +1 , 8)

OldestDate Expression:

(DT_I4) (RIGHT( @[User::Date] , 4)+ SUBSTRING( @[User::Date] , 3,2)+ SUBSTRING( @[User::Date] ,1, 2))

Script Task - I

Public Sub Main()
        Dim OldestDate As Integer
        Dim CurrentDate As Integer
        OldestDate = CType(Dts.Variables("OldestDate").Value, Integer)
        CurrentDate = CType(Dts.Variables("CurrentDate").Value, Integer)
        If OldestDate > CurrentDate Then
            Dts.Variables("OldestFile").Value = Dts.Variables("Filename").Value.ToString()
            Dts.Variables("CurrentDate").Value = CType(Dts.Variables("OldestDate").Value, Integer)
        End If
        Dts.TaskResult = Dts.Results.Success
    End Sub

Script Task - II

Public Sub Main()
        MsgBox(Dts.Variables("OldestFile").Value.ToString())
        Dts.TaskResult = Dts.Results.Success
End Sub

Explanation:

The basic idea here is get the date part i.e. DDMMYYYY and convert it to YYYYMMDD. Now, we can compare which date is greater by simple integer comparision as long as the date is in YYYYMMDD format. We start with comparing the first file's date with 19000101 ( a date well in past) and then as seen in the Script task - I, we assign the OldestFile with the Filename conditionally. The Script Task - II printsout the filename with the oldest date.