How to feed recent file in a folder to a Flat File Source in Integration Services

Question

I want to import a CSV file from a folder that daily similar files. Since I need to truncate the destination sql server before inserting, I need only the most recent file from the source. I am creating a Integration Services (dtx) package that can be run without manually changing the source file name

Answer 1

You have to create a script task and use below code:

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

The above code is just a reference, you have to modify as per your own Variables and also filter by your own file name convention