Several of my DTS jobs require processing a file and then archiving the file. As we all know, you can use ActiveX to Copy or Move a file. Problem is, sometimes we need to reprocess or reuse a file name. When the archive ActiveX step executes, the step will fail if the file already exists in the archive directory. In order to ensure that the archive step succeeds, we need to set a few flags. We will then take those flags and use them as parameters for the Copy command. We will not use the move command since it does not have the option to use these flags.
'**********************************************************************
' Visual Basic ActiveX Script
'***********************************************************************
Function Main()
Dim oFSO
Dim vSourceFile
Dim vDestinationFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const OverwriteExisting = True
Const DeleteReadOnly = True
' Set the source file and destination directory
' You can use the file name or wildcards
vSourceFile = "\\SourceDirectory\*.csv"
vDestinationFile = "\\Archive\"
' Move the file(s) to the target directory
objFSO.CopyFile vSourceFile, vDestinationFile, OverwriteExisting
' Delete the original file
objFSO.DeleteFile(vSourceFile ) , DeleteReadOnly
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
The code above will move the file or files to the target directory, overwriting any files with the same filename and delete the source files in the source directory.
