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

user_16251796 0
Viewed 144

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.

ssis containers sql-server loops
2 Answers

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;
user_33252900

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?

user_46067870
Related