CODEWITHSUNDEEP
sql-servervb.netssisetlscript-task
SJJ9166
SJJ9166

Reputation: 73

Passing filename from SSIS script task

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

Upvotes: 2

Views: 4813

Answers (1)

Hadi
Hadi

Reputation: 37368

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:

Upvotes: 1

Related Questions