Guys, sorry, Im just back from a holiday.
Absolutely NOT, those lines need to be UNCOMMENTED in order for the actual deletion operation to work properly. I commented these as a safety net so I wouldn't be deleting files from an environment that was "off-limits".
Great catch, I should've mentioned it in the article.
I've also verified the script against that which is running on our production box and indeed the files are being deleted and no errors returned.
I've included the Function Main() below:
Function Main()
Dim objFSO
Dim objFolder
Dim objFileCollection
Dim strSourceFile
Dim varToday
Dim intFileCount
Dim intX
varNumber = DTSGlobalVariables("gvNumber").Value
varFilemask = DTSGlobalVariables("gvFileMask").Value
varFolderPath = DTSGlobalVariables("gvFolderPath").Value
varToday = Date
'Write variables out to debug log
Call DebugLog
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(varFolderPath)
Set objFileCollection = objFolder.Files
intFileCount = objFileCollection.Count
ReDim arrFileList(intFileCount)
intX = 0
'Fill the array with the files
For Each objFile in objFileCollection
If inStr(1, objFile.name, varFileMask, 1) > 0 Then
Set arrFileList(intX) = objFile
intX = intX + 1
End If
Next
ReDim Preserve arrFileList(intX-1)
intFileCount = UBOUND(arrFileList)
'If there are more than X files in the folder
If intFileCount > varNumber Then
'Sort the array using Bubble Sort
Dim startingKey
Dim mostRecent
Dim swapPos
Dim ValueToCompare
'Loop thru array and move most recent files to top
For i = 0 To UBOUND(arrFileList) - 1
'use first element as start and most recent value
set startingKey = arrFileList(i)
set mostRecent = arrFileList(i)
swap_Pos = i
for k = i + 1 To UBOUND(arrFileList)
'if most Recent date is earlier the next in array, swap them
if mostRecent.DateLastModified < arrFileList(k).DateLastModified then
swap_pos = k
Set mostRecent = arrFileList(k)
End If
next
If swap_Pos i Then
Set arrFileList (swap_pos) = startingKey
Set arrFileList(i) = mostRecent
End If
Set startingKey = Nothing
Set mostRecent = Nothing
Next
'Delete the files from the folder
For m = 4 To UBOUND(arrFileList)
Call LogFileName(arrFileList(m).name & " " & cStr(arrFileList(m).DateLastModified))
objFSO.DeleteFile arrFileList(m)
Next
End If 'more than X files in folder
' Clean Up
Set objFile = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
Set objFileCollection = Nothing
'Set arrFileList = Nothing
Main = DTSTaskExecResult_Success
End Function
Here is the calling SQL step syntax:
DTSRUN /SmyServer /N"Purge Files by Number - DTS" /AgvFileMask:8=".bak" /AgvFolderPath:8="C:\myPath\ImportBackup\" /AgvLogPath:8="C:\myPath\ImportBackup\DTSPurgeLog_Number.log" /AgvNumber:3=4 /E
Here's an extract from the log file:
Logging File Purge Activity: 2/15/2006 10:13:04 AM
----------------------------------------
Path = C:\Customers\AmericanAirlines\ImportBackup
Mask = .bak
Files to keep = 4
AA_SMPv212_20060209.bak 2/9/2006 5:02:26 AM DELETED
AA_SMPv212_20060208.bak 2/8/2006 5:02:28 AM DELETED
AA_SMPv212_20060207.bak 2/7/2006 5:02:23 AM DELETED
AA_SMPv212_20060206.bak 2/6/2006 5:02:26 AM DELETED