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, Apr 26 '18 at 18:10
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, Apr 26 '18 at 18:32
So, if you can predict the names of the files, why not just do that? — STLDev, Apr 26 '18 at 18:37
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, Apr 26 '18 at 18:49
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.Mc, Jul 09 '18 at 04:48

score 0 · Answer 1 · answered Apr 26 '18 at 19:49

0

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.

answered Apr 26 '18 at 19:49

Tab Alleman

31,483
7
36
52

I am a Junior Data analyst and not advanced coder, my manager came up with requirement and wanted to get this done, sample expression for above would be helpful – Brita Apr 26 '18 at 21:56
There are samples in the link I provided. Stack Overflow is not a site where programmers work for free. If the requirement is above your skill level, your manager should hire a programmer. – Tab Alleman Apr 27 '18 at 13:14
Actually was lucky to find similar scenario here, Fixed my isssue.https://stackoverflow.com/questions/6922517/how-do-i-format-date-value-as-yyyy-mm-dd-using-ssis-expression-builder – Brita Apr 27 '18 at 13:20
I thought it was a free site for sharing if I misuderstood the purpose of stackoverflow..I will give you credit for the link, I didnt see in my mobile phone..pragmatic site seems to have really useful samples. – Brita Apr 27 '18 at 13:30

Load latest flat files via SSIS

1 Answers1