2

I'm attempting to create a SSIS package that loads a flat file into a SQL server table.

I've been able to piece the loading functionality together. I'm currently stuck on passing the filename if it's found from the script task back to a variable where I'd like to use it in the flat file connection string.

Public Sub Main()
        '
        Dim di As DirectoryInfo = New DirectoryInfo("\\winshare\iFile\Cors2\AAA\AAA Employee Incentive Source Data\")

        Dim fi As FileInfo() = di.GetFiles("AAA Full PreReg Report*.csv")

        If fi.Length > 0 Then
            Dts.Variables("User::fileExists").Value = True
            Dts.Variables("User::FileName").Value = fi.name
        Else
            Dts.Variables("User::fileExists").Value = False
        End If

        ' Add your code here
        '
        Dts.TaskResult = ScriptResults.Success
    End Sub

I'm seeking help with Dts.Variables("User::FileName").Value = fi.name

Why won't this work?

Thanks

Hadi
  • 36,233
  • 13
  • 65
  • 124
SJJ9166
  • 71
  • 1
  • 6
  • 2
    Is there a reason you don't use the out of the box feature of `Foreach Enumerator`? – billinkc Jul 22 '19 at 16:25
  • 1
    is it a data type issue as you are not casting? It seems like fi might be an array and not a string – KeithL Jul 22 '19 at 18:13
  • What does not work ? Are you getting any exceptions ? Have you set your variable as [Read/Write][1] ? [1]: https://stackoverflow.com/questions/51598743/extracting-substring-from-a-filename-and-saving-it-to-a-variable-in-ssis/51599285#51599285 – Subbu Jul 23 '19 at 02:07

1 Answers1

1

If you are looking to get the first file in the directory then you can use the following line of code:

Dts.Variables("User::FileName").Value = fi(0).name

But If you are looking to loop over files then i recommend using the Foreach loop container to loop over files and store each file name within a variable:

Hadi
  • 36,233
  • 13
  • 65
  • 124