SSIS: import MAX(filename) from folder

2019-08-15 04:09发布

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.

1条回答
SAY GOODBYE
2楼-- · 2019-08-15 04:48

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

enter image description here

  • 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
查看更多
登录 后发表回答