CODEWITHSUNDEEP
sql-servervb.netssisetlscript-task
intruder
intruder

Reputation: 417

File extension Script task and Error handling

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

Upvotes: 3

Views: 451

Answers (2)

Hadi
Hadi

Reputation: 37368

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

Upvotes: 1

sree
sree

Reputation: 1960

Below would be my suggestion, I did the same in one of my requirements using event handling section that the underlying DFT is not run, then the script in event handler page would raise error. The point to be noted is that the DFT runs atleast once if there is any file in directory, raising error if it not runs would be simple rather than writing a complex script

Thanks, Srinivas

Upvotes: 0

Related Questions