Take the oldest file in foreach loop

foreach-loop-container, ssis

Solution

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.

Problem

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 ?

Original source