Move a file with ActiveX in DTS

Once you process a file, you will usually archive it. Often, you may run into cases where the file already exists in the archive directory. Unfortunately, the Move command does not have an overwrite option. You will need to use the Copy command with the Overwrite option.

Function Main()
Dim oFSO
Dim vSourceFile
Dim vDestinationFile

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const OverwriteExisting = True
Const DeleteReadOnly = True

vSourceFile = "\\MySourceDirectory\*.csv"
vDestinationFile = "\\MyDestinationDirectory\"

' Move the files Use OverwriteExisting in case the file is there
objFSO.CopyFile vSourceFile, vDestinationFile, OverwriteExisting

' Delete the files in the source directory
objFSO.DeleteFile(vSourceFile ) , DeleteReadOnly

Set oFSO = Nothing

Main = DTSTaskExecResult_Success

End Function

Now, all of the files in the source directory are now in the archive.

Grab the filename for a file in a Folder

You will probably come across a file that is formatted like this

filename_20100101235938.csv

where we are dealing with the full timestamp in the name. As long as the file is the only file in the directory, we can use the script below:

Function Main()

Set objFSO =
CreateObject("Scripting.FileSystemObject")
objStartFolder = "\\DriveName\FolderName\"

Set objFolder = objFSO.GetFolder(objStartFolder)

Set colFiles = objFolder.Files
For Each objFile in colFiles
DTSGlobalVariables ("gv_FileName").Value =
objStartFolder & objFile.Name
Next
Main = DTSTaskExecResult_Success

End Function

You now have the full path and filename in the global variable. Now, if there are multiple files in the directory, you will have the last file name in the global variable.