File extension Script task and Error handling

Question

I've literally no experience in VB script or C#. I've created this SSIS package using some online tutorial which server my purpose but I've to fine-tune it to fit my requirements.

Current Scenario:

I'm trying to run an SSIS package which has a for-each loop container which imports the files with *.txt extension in a directory as the file names are not constant. This for-each loop container is followed by some other SQL tasks.

The package is executed successfully even when there are no files in the directory (May be I did something wrong while creating the container and data flow tasks, file system tasks). This is causing the SQL script at the end of the for-each loop container to execute successfully which is resulting in wrong data.

Requirement:
The package should fail if there is no file in directory. I've to implement a script before for-each loop container but not sure how to do it. Any leads would be appreciated!

I did something like this but not sure how to search wrt extension rather than file name:

Public Sub Main()
    '
    ' Add your code here
    '
    Dim fileName As String
    fileName = "filename.txt"

    If System.IO.File.Exists(fileName) Then
        Dts.Variables("User::bolFileExists").Value = True
    Else
        Dts.Variables("User::bolFileExists").Value = False
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

Hadi Hadi · Accepted Answer · 2019-01-15T09:30:29

You should use System.IO.Directory.GetFiles() function.

If System.IO.Directory.GetFiles(<your path goes here>, "*.txt", SearchOption.AllDirectories).Length = 0 Then 
   Dts.Variables("User::bolFileExists").Value = False
Else
    Dts.Variables("User::bolFileExists").Value = True
End If

File extension Script task and Error handling

2 Answers