Excellent use of SSIS

however I did spot a potential bottleneck, you could potentially get stuck in loop if the process that held the file open has halted or crashed or ended with a file lock still in place

Consider the following as a replacement for your wait code, Hope this proves useful at times when things get stuck 🙂

private void WaitForExclusiveAccess(FileInfo dataFile)

{

// local variable to say how many seconds to wait in between checking if we can gain

// exclusive access to the found file

int secondsToWaitBetweenAttempts = 5;

// local variable to pass to events that require parameters be passed by ref

bool fireAgain = true;

int Loopcount = 1;

int EndlessLoopCount = 10;

//multiple of the interval between open attemtps ie 10 in this case will lapse after 60 seconds of trying to open the file

// Loop indefinitely checking if we can access the data file.

while (1 == 1) {

if (Loopcount > EndlessLoopCount) {

string strError = string.Format("failed to gain exclusive access to file {0} . Waited {1} seconds trying every {2} Seconds, Exiting package", foundFile.FullName, secondsToWaitBetweenAttempts * EndlessLoopCount, secondsToWaitBetweenAttempts);

Dts.Events.FireInformation(0, null, strError, string.Empty, 0, true);

throw new TimeoutException(strError);

}

try {

// Attempt to gain access to the file.

using (Stream stream = new FileStream(dataFile.FullName, FileMode.Open)) {

// If we made it here no exception was thrown meaning we

// could access the file. We will break out of the loop and allow

// the rest of the package to continue processing.

break; // TODO: might not be correct. Was : Exit Try

}

// We are not interested in ending the program when an IOException

// occurs in this area. This type of exception means we could not

// gain access to the file.

// In general, programming algorithms that leverage exceptions for

// control flow are frowned upon. However in the case of file access

// it is an acceptable pattern.

} catch (IOException generatedExceptionName) {

}

// raise an information event saying we could not gain exclusive access to the found file and will wait

Dts.Events.FireInformation(0, null, "Could not gain exclusive access to file " + foundFile.FullName + ". Waiting " + secondsToWaitBetweenAttempts.ToString() + " seconds before trying again...", string.Empty, 0, fireAgain);

// wait some time before checking whether the file can be used

Threading.Thread.Sleep(secondsToWaitBetweenAttempts * 1000);

Loopcount += 1;

}

}

or if people have converted to VB

Private Sub WaitForExclusiveAccess(ByVal dataFile As FileInfo)

' local variable to say how many seconds to wait in between checking if we can gain

' exclusive access to the found file

Dim secondsToWaitBetweenAttempts As Integer = 5

' local variable to pass to events that require parameters be passed by ref

' EndlessLoopcount multiple of the interval between open attemtps ie 10 in this case will lapse after 60 seconds of trying to open the file

Dim fireAgain As Boolean = True, Loopcount As Integer = 1, EndlessLoopCount As Integer = 10

' Loop indefinitely checking if we can access the data file.

While 1 = 1

If Loopcount > EndlessLoopCount Then

Dim strError As String = String.Format("failed to gain exclusive access to file {0} . Waited {1} seconds trying every {2} Seconds, Exiting package", foundFile.FullName, secondsToWaitBetweenAttempts * EndlessLoopCount, secondsToWaitBetweenAttempts)

Dts.Events.FireInformation(0, Nothing, strError, String.Empty, 0, True)

Throw New TimeoutException(strError)

End If

Try

' Attempt to gain access to the file.

Using stream As Stream = New FileStream(dataFile.FullName, FileMode.Open)

' If we made it here no exception was thrown meaning we

' could access the file. We will break out of the loop and allow

' the rest of the package to continue processing.

Exit Try

End Using

' We are not interested in ending the program when an IOException

' occurs in this area. This type of exception means we could not

' gain access to the file.

' In general, programming algorithms that leverage exceptions for

' control flow are frowned upon. However in the case of file access

' it is an acceptable pattern.

Catch generatedExceptionName As IOException

End Try

' raise an information event saying we could not gain exclusive access to the found file and will wait

Dts.Events.FireInformation(0, Nothing, "Could not gain exclusive access to file " + foundFile.FullName + ". Waiting " + secondsToWaitBetweenAttempts.ToString() + " seconds before trying again...", String.Empty, 0, fireAgain)

' wait some time before checking whether the file can be used

Threading.Thread.Sleep(secondsToWaitBetweenAttempts * 1000)

Loopcount += 1

End While

End Sub