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

for the file to be usable. Note that if the file remains in use at this juncture, we still
move on. We'll deal with the file-in-use matter later, but we will not hang ourselves in
a potentially endless loop waiting for the file's availability. We will instead fail. Wheth-
er the file is in use or not in use, the script logs its state at comment 7.
At comment 8, we check for the existence of the file and begin a Try-Catch. If the
file doesn't exist, I opt to log a status message (via
Dts.Events.FireInformation ) and continue (see comment 8g). The Try-
Catch enforces the final test of the file's usability. If the file remains in use here, the
Catch fires and logs the status message at comment 8f. At 8f and/or 8g, you may very
well decide to raise an error using the Dts.Events.FireError method. Raising
an error causes the Script task to fail, and you may want this to happen. At comments
8a through 8d, we open the file, append the footer row, close the file, and clean up. At
comment 8e, the code logs a status message. If anything fails when we are executing 8a
through 8e, code execution jumps to the Catch block.
If all goes well, the code returns Success to the SSIS control flow via the
Dts.TaskResult function (comment 9).
The Script task does all the work in this pattern. Close the Script Task Editor. Click
OK. Then save the package.
I created a test package called TestParent.dtsx to test this package. The
package has variables that align with the parameters of the WriteFileFoot-
er.dtsx package, as shown in Figure 7-22 .
Figure 7-22 . Variables in the TestParent.dtsx package
If you're playing along at home, you should adjust the path of the Ex-
tractFooterFilePath variable.
I added a Sequence container named seq Test WriteFileFooter and included an Ex-
ecute Package task named ept Execute WriteFileFooter Package. On the Package page
of the Execute Package Task Editor, set the ReferenceType property to Project
Reference and select WriteFileFooter.dtsx from the Pack-