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.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter