SSIS: import MAX(filename) from folder

Question

I need to pick one .csv file from \\\Share\Folder\ with max filename for further import to SQL. File name is alphanumerical, e.g. ABC_DE_FGHIJKL_MNO_PQRST_U-1234567.csv, where numerical part will vary, but I need the only max one each time the package runs.

Constraints: no write access on that SQL server, I use ##Temp table for import and this is the least desirable method for filename processing (no for each loops on this server).

Ideally it will be function/expr-based variable (combined with script task if needed) to pass into connection manager. Any ideas much appreciated.

Answer 1

Use a Script Task

• Add a variable of type String User::CsvFile
• Add a script task to your project and add your created variable as a ReadWriteVariable

• In Your Script task write the following code (VB.NET):

You have to Import System.Linq Library

Public Sub Main()

    Dim strDirectory As String = "C:\New Folder" ' Enter =the directory
    Dim strFile As String = String.Empty

    strFile = IO.Directory.GetFiles(strDirectory, "*.csv", IO.SearchOption.TopDirectoryOnly).OrderBy(Function(x) x.Length).Last


    Dts.Variables.Item("CsvFile").Value = strFile

    Dts.TaskResult = ScriptResults.Success

End Sub

• Then use this variable from Flat File Source