How to read all files of a folder based on their creation date in SSIS?

Question

I've a folder and each day a few flat files get copied into it. The files have the same format and same structure. However, I want to read them into my database based on their creation order.

Example: the file that was created at 1 pm must get processed before the one that was created at 2:pm. I was wondering if there is any simple trick for doing this? Oddly, I have to use SSIS 2008! Thanks!

Answer 1

The Good Thing is that you are using SSIS 2008 because it is allow Linq

My illustrations are for SSIS 2015 but it works with 2008

• Add a variable of type Object User::dtFiles
• Add a script task to your project and add your created variable as a ReadWriteVariable

• In Your Script task write the following code:

You have to Import System.Linq Library

    Public Sub Main()
    '
    ' Add your code here
    '


    Dim strDirectory As String = "C:\New Folder"
    Dim dtFiles As New List(Of String)

    dtFiles.AddRange(IO.Directory.GetFiles(strDirectory, "*.*", IO.SearchOption.TopDirectoryOnly
                                           ).OrderBy(Function(x) IO.File.GetCreationTime(x)).ToList)


    Dts.Variables.Item("dtFiles").Value = dtFiles

    Dts.TaskResult = ScriptResults.Success
End Sub

• Add a for each Loop Container

• Change The for each loop to ado enumerator and set the datasource to User::dtFiles