Flat File Source Patterns - SQL Server Integration Services Design Patterns - page 211

If File.Exists(sFile) Then
Try
Dim f As Integer = FreeFile()
FileOpen(f, sFile, OpenMode.Binary,
OpenAccess.ReadWrite, _
OpenShare.LockReadWrite)
FileClose(f)
Catch ex As Exception
Return True
End Try
End If
End Function
The script now builds the footer row and appends it to the Extract file. The first
thing we do—at the comment labeled 1—is assign a value to the Debug variable. I use
the Debug variable to control message boxes displaying variable values and other per-
tinent information. I describe why in Chapter 2 on execution patterns.
At comment 2, we declare and initialize variables. I break variables into two cat-
egories: generic and task-specific variables. At comment 3, we build a message in the
variable sMsg . This message contains the values of each variable used in the script
thus far. If we are running in Debug mode (if bDebug is True ), the code displays a
message box (via the MsgBox function) containing the contents of sMsg . Whether
we're running in Debug mode or not, I use the Dts.Events.FireInformation
method to raise an OnInformation event, passing it the contents of sMsg . This
means the information is always logged and is optionally displayed by a message box. I
like options (a lot).
Comment 4 has us constructing the actual footer row and placing its text in the
String variable sFooterRow . Note the delimiter is also dynamic. The String variable
sDelimiter contains the value passed to the WriteFileFooter into the Pack-
age parameter named $Package::Delimiter . At comment 5, we log the contents
of the footer row.
At comment 6, we initiate a check to make sure the extract file is not marked as “in
use” by the operating system. There are many ways to detect the state of the file in the
file system, so I created a Boolean function named FileInUse to encapsulate this
test. If the function I created doesn't work for you, you can construct your own. If the
file is in use, the code initiates a While loop that sleeps the thread for one second. Each
iteration through the loop causes the variable s (the incrementor in this example) to in-
crement at comment 6b. If s exceeds ten, the loop exits. We will only wait 10 seconds