Load latest flat files via SSIS

Question

I have to load 2 flat files into a SQL Server table. Flat files are loaded to a folder. It has thousands of other files. If it was same file with different dates I would have used foreach loop and done it..but here is the scenario.

File names I want to load are as follows:

Non_Payment_Stat_Data1_12_2017.txt
Payment_Stat_Data1_12_2017.txt
Files are loaded daily
I need to load just above file type to table (pick the days load)

There are many other files some of which are Payment_Stat_Data or Non_Payment_Stat_Data without the date part at the end. We don't want to load these into the table.

I tried using script task c# code and it gave me latest file but not the one we wanted to load.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_2650e9fc7f2347b2826459c2dce1b5be.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            // TODO: Add your code here
            var directory= new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());

            FileInfo[] files = directory.GetFiles();
            DateTime lastModified = DateTime.MinValue;

            foreach (FileInfo file in files)
            {
                if (file.LastWriteTime > lastModified)
                {
                    lastModified = file.LastWriteTime;
                    Dts.Variables["User::VarFileName"].Value = file.ToString();
                }
            }

            MessageBox.Show(Dts.Variables["User::VarFileName"].Value.ToString());

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Source:http://www.techbrothersit.com/2013/12/ssis-how-to-get-most-recent-file-from.html

The code works but gives another latest flat file.. I only want to pull Non_Payment_Stat_Data1_12_2017.txt and Payment_Stat_Data1_12_2017.txt files. They will have the date changing every day.

Your question is a bit confusing. You say you want to grab the latest file, but that when you do that, it's not the file you want. Then you mention a couple of static file names and say that you always want to choose those files. Which is it? — STLDev
The 2 files that look static like Non_Payment_Stat_Data1_12_2017 and Payment_Stat_Data1_12_2017 actually change daily, ie the date part keep changing everyday, we want to grab those 2 files with new dates everyday and load. Example when date is 1_13_2017 for above 2 files we load them and ignore the old files(not reload them) — Brita
So, if you can predict the names of the files, why not just do that? — STLDev
I didn,t get it. Yes file name stays same but those date changes, all the old flat files are still in the same folder, we want to load only the fresh new files for above 2 types. — Brita
Use an expression to build the file name based on the day. But this is usually a bad idea. You should write your package so that it will work correctly on any day, even if you run it for a file you already loaded. — Nick.McDermaid

Tab Alleman Tab Alleman · Accepted Answer · 2018-04-26T19:49:39

If the file is named in a predictable fashion based on today's date (which you seem to be saying it is), then just use an expression for the file name(s) in the Flat File Connection Manager.

Load latest flat files via SSIS

1 Answers