Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTS and Global Variables


DTS and Global Variables

Author
Message
Alex-217289
Alex-217289
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 3
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aKersha/dtsandglobalvariables.asp


Cheers,

Alex


Rogue DBA
SQLGuy64
SQLGuy64
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 291
Great article.
Grant Sutcliffe
Grant Sutcliffe
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10

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

to

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


Steve DiDomenico-197994
Steve DiDomenico-197994
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 5

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

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


Alex-217289
Alex-217289
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 3
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


Cheers,

Alex


Rogue DBA
Steve DiDomenico-197994
Steve DiDomenico-197994
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 5

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:

TestFile1.bak
TestFile2.bak
TestFile3.bak
TestFile4.bak
NorthWind1.bak
NorthWind2.bak
NorthWind3.bak

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

TestFile1.bak
NorthWind1.bak
NorthWind2.bak
NorthWind3.bak

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


Steve DiDomenico-197994
Steve DiDomenico-197994
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 5

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


David le Quesne
David le Quesne
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 32
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 !



David

If it ain't broke, don't fix it...
Henry_Lee
Henry_Lee
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 1658
great detail, that was big help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search