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

Question

I need to create a process in SSIS that does the following:

1. Get all text files in a specific directory
2. Sort those files in creation order (some files alter the same table, so I need the most recent file to have the last say in those records being inserted/updated)
3. Process each file
4. For each record on that text file I need to insert that record in an sql table or update that record if it already exists in the database

What is the best approach to do this?

I'm using SQLServer 2016

Help would be appreciated.

Answer 1

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

        string[] files = new System.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;