Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS and Global Variables Expand / Collapse
Author
Message
Posted Tuesday, January 31, 2006 6:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 9, 2009 8:13 AM
Points: 75, 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
Post #254822
Posted Monday, February 20, 2006 7:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:15 AM
Points: 55, Visits: 232
Great article.
Post #259849
Posted Tuesday, February 21, 2006 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2008 2:33 AM
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

Post #260271
Posted Wednesday, February 22, 2006 8:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 23, 2013 11:09 PM
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

Post #260643
Posted Wednesday, February 22, 2006 9:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 9, 2009 8:13 AM
Points: 75, 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
Post #260656
Posted Wednesday, February 22, 2006 12:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 23, 2013 11:09 PM
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

Post #260732
Posted Thursday, February 23, 2006 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 23, 2013 11:09 PM
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

Post #260949
Posted Sunday, February 26, 2006 6:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, 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...
Post #261514
Posted Thursday, February 8, 2007 7:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 320, Visits: 1,485
great detail, that was big help!
Post #343445
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse