Database Reference
In-Depth Information
Dts.TaskResult = ScriptResults.Success
End Sub
As in other scripts, we declare (
Dim
) a variable named
bDebug
to detect whether
the package is executing in Debug mode at comments 1 and 2. At comment 3, the
script declares the remainder of the variables used, assigning some values passed in
from SSIS package variables and parameters. At comments 4a through 4c, the code
picks the
Source File Path
variable apart, parsing the source directory, the file-
name with extension, and the filename without extension. At comments 4d through 4f,
the filename extension is parsed and a filename for a “working copy” is created and
formatted with the datetime stamp supplied from the SSIS package parameters. At
comment 4g, the script assigns variable values to SSIS package variables. The code
between comments 4h and 4m tests and responds to the existence of the source file. If
an exception is encountered in any of the steps between comments 4a and 4m, the
Catch
block at comment 4n is executed and logs the exception as an error, which
halts the execution of the Script task. The code at comment 5 builds, displays (if run-
ning in Debug mode), and logs a message containing the variable values inside the
Script task. This is extremely useful information when troubleshooting. At comment 6,
the script returns a
Success
result to the
Dts.TaskResult
object.
The remaining steps in the file archive process are as follows:
1. Create a working copy of the source file.
2. Rename the working copy to the
Formatted File Name
(including the
datetime stamp).
3. Move the newly-renamed file to the archive directory.
4. Delete the original file (unless this is a
CopyOnly
operation).
If the
OkToProceed
(Boolean) package variable is set to
True
(this is accom-
plished in the script code at comment 4i), the remaining steps in the process are man-
aged by File System tasks.
Drag four File System tasks onto the control flow canvas. Rename the first
fsys
Copy Working File
and open its editor. Change the
Operation
property to
Copy
File
. Set the
IsSourcePathVariable
property to
True
and the
SourceVariable
property to
$Package::SourceFilePath
. Set the
IsDestinationPathVariable
to
True
and set the
DestinationVariable
property to
User::WorkingCopyFileName
. Set the
OverwriteDestina-
tion
property to
True
. Close the File System Task Editor.
Search WWH ::
Custom Search