how to pick the first file first from specific folder using for each in SSIS

Question

I have a remote folder from where i pick the multiple files and loop through for each loop container. But I want to pick the first file first based on time stamp from that folder. how do I do this in SSIS?

Answer 1

1. First you hace to create 2 Variables

   FolderPath (string) -- to store the folder you have to manipulate
   
   dtFiles (Object)   -- to store files from this folder
   

2. Add a script task and select FolderPath as ReadOnlyVariable and dtFiles as ReadWrite Variable

3. In the script write the following code

    Imports System.Collections.Generic
    Imports System.Linq
   
   Public Sub Main()
   
   Dim strFolderPath As String = Dts.Variables.Item("FolderPath").Value.ToString
   
   Dim lstFiles As New List(Of IO.FileInfo)
   
   For Each strFile As String In IO.Directory.GetFiles(strFolderPath, "*.*", IO.SearchOption.AllDirectories)
   
       Dim fi As New IO.FileInfo(strFile)
   
       lstFiles.Add(fi)
   
   Next
   
   Dts.Variables.Item("dtFiles").Value = lstFiles.OrderBy(Function(x) x.CreationTime).Select(Function(x) x.FullName).ToList
   
   
   End Sub
   

4. Connect your script task to The For each loop Container

5. Double Click on the ForEach Loop container and change the enumerator type to ADO enumerator and choose the variable dtFiles as a source (in the collection tab) and choose the enumeration mode (Rows from first table)

6. In the variable mapping tab (in For each loop container) map the index 0 to a new Variable i.e. FileName (You can use it to do your work)

Note: i used sorted files using CreationTime. You can even use LastAccessTime and LastWriteTime properties

Just add a value to FolderPath variable and Execute