By Ken Whiteside

DataXperts

Checking if Files Exist ( by Wildcard )

When using the For Each Loop Container you will notice that by design, if there are no files, it will simply succeed. This is by the design of the Microsoft team. However, if you are counting on files being there, and the fact that there are not files means some delivery agent has failed, you may want to be alerted to this. I have found the best way is to use a vbScript task just above the “for each” container that checks for the existence of any file matching the same pattern that the for each container will be looking for. If there are not any, fail the whole package.

Double Click the Script Task –

Click Script on the left pane, and then click the Design Script button to see the vbscript.

This will open the Visual Studio environment where you can place your code.

Public Sub Main()

' Get file name from the "Flat File" Connection

Dim Dirname As String = "\\90.164.189.56\Connect_Direct\Active\BERTPLUS\"

Dim filename As String = "??dordata.txt"

Dim ReturnName As Array

ReturnName = System.IO.Directory.GetFiles(Dirname, filename)

If ReturnName.Length < 1 Then

Dts.TaskResult = Dts.Results.Failure

Else

Dts.TaskResult = Dts.Results.Success

End If

End Sub

This code is pretty straight forward. Call the GetFiles to see if any files mathing the wildcard exists. If it finds any, the ReturnName.Length will be greater than zero. If not it will be zero and you can force the TaskResult to DTS.Results.Failure.

On the properties of the VBScript task

Set the FailPackageOnFailure to True. This way you will know the files that were expected to be delivered, did not make it there for you. Now you can contact the responsible party and see what happened.