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.

Have you tried using a for each loop to load into an ADO recordset then MAX out of that? (the second part I'm not so sure on) — Nick.McDermaid
You can do this with a script task, more details in the answer below — Hadi

Hadi Hadi · Accepted Answer · 2018-02-04T23:42:51

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

SSIS: import MAX(filename) from folder

1 Answers

Use a Script Task