Ssis - Loop Through Text Files Sorted In Creation Order And Process Each Record On Each File As An Update Or An Insert Into A Sql Table

I need to create a process in SSIS that does the following: Get all text files in a specific directory Sort those files in creation order (some files alter the same table, so I ne

Solution 1:

I don't know a way to guarantee sort order in SSIS but here is a c# solution for script task:

string[] files = newSystem.IO.DirectoryInfo(@"C:\path").GetFiles()
                               .Where(e => e.Extension.EndsWith("txt"))
                               .OrderBy(d => d.CreationTime)
                               .Select(f => f.FullName)
                               .ToArray();

        foreach(var f in files)
        {
             //Do your work here or pass out to variable to be processed in SSIS
        }

Make sure that the following Namespace is in your code:

using System.Linq;

Solution 2:

There is a perfect, and widely used, task for this already in SSIS called the foreach task. This will grab each file in a directory (you can specify a mask so it grabs all the csv or txt type files (1) (3) and then within a container you can pass in file and process it with a data task(step 4). You probably will add one or more tasks to move/archive/delete the text file after it has been processed.

I don't understand what the point of step 2 is. You probably want to process all the files in a given folder. Why would the order matter?