DTS and Global Variables

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aKersha/dtsandglobalvariables.asp



    Rogue DBA

  • Great article.

  • Perfect Article for a person (like me) starting to dip into DTS. Thanks.

    Needed 1 change to be able to delete the files:

    Change line: arrFileList(intX) = objFile = intX + 1


    arrFileList(intX) = objFile

    intX = intX + 1

    Code worked when I changed it to VB6 module but there was an error on line 63: set startingKey = arrFileList(i)

    Error message for the job: Object required: '[string: "C:\Books\asp.net.pdf"]'

    No Problem in VB is used the File Object here. It might be just me??

    Thanks again - I learned a lot

  • Great article. 

    I was able to get everything to work except the actual deleting of files. Also, the log file is created but it does not show a list of deleted files, so this is consistant.

    Are these line supposed to be commented in:

    'Delete the files from the folder and log their names

      For m = 4 To UBOUND(arrFileList)

       'Call LogFileName(arrFileList(m).name & " " & cStr(arrFileList(m).DateLastModified))

        'objFSO.DeleteFile arrFileList(m)


    As a test,I commented them back in, but the delete did not work.

    When I try the suggestion from Grant Sutcliffe, I get an error on line # 64.

    Here is the command I'm using and there are (4) .bak files in it:

    DTSRUN /SUCEASQL /N"Purge Files by Number - DTS" /AgvFileMask:8=".bak" /AgvFolderPath:8="D:\Production\PROD\Developement\DeleteTest" /AgvLogPath:8="D:\Production\PROD\Developement\DeleteTest\Temp\DTSPurgeLog_Number.log" /AgvNumber:3=1 /E

  • 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


    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


    If swap_Pos i Then

    Set arrFileList (swap_pos) = startingKey

    Set arrFileList(i) = mostRecent

    End If

    Set startingKey = Nothing

    Set mostRecent = Nothing


    '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)


    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



    Rogue DBA

  • My last test was interesting. Earlier I was testing with (4) files I created using Notepad. I was unable to get these files to delete. Then I created (3) files by backing up the NorthWind Database. So here are the files I had:








    Then I ran the job again and some files where deleted. Here are the file that were not deleted:





    Here is my log file showing a Mask = .bak and Files to keep = 2:

    Logging File Purge Activity: 2/22/2006 1:42:55 PM


    Path = D:\Production\PROD\Developement\DeleteTest

    Mask = .bak

    Files to keep = 2


    TestFile2.bak 2/22/2006 9:12:00 AM DELETED


    TestFile3.bak 2/22/2006 9:12:00 AM DELETED


    TestFile4.bak 2/22/2006 9:12:00 AM DELETED

  • Here is why there were (4) files kept when I only wanted to keep (2) as specified using param /AgvNumber:3=2 in the Command:

    For m = 4 To UBOUND(arrFileList)

    When I change this line of code to use the input variable of the number of files to keep, it works as expected.

    For m = varNumber To UBOUND(arrFileList)

    This is a really nice tool and an easy implementation.

    Thanks again

  • Hi,

    I also get the Object Required error at line 64

    Error: -2147220482 (800403FE); Provider Error: 0 (0)

    Error string: Error Code: 0

    Error Source= Microsoft VBScript runtime error

    Error Description: Object required: 'C:\temp\RubbishFile_'

    This is not the full filename, it appears to be truncated.

    Update: Pasted in the 'live' version of Function Main() and it worked !


    If it ain't broke, don't fix it...

  • great detail, that was big help!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply