SQLServerCentral / Discuss Content Posted by Alex Kersha / Article Discussions / Article Discussions by Author / DTS and Global Variables / Latest Posts

RE: DTS and Global Variables

Henry_Lee — Thu, 08 Feb 2007 07:17:00 GMT

great detail, that was big help!

RE: DTS and Global Variables

David le Quesne — Sun, 26 Feb 2006 06:21:00 GMT

Hi,I also get the Object Required error at line 64 Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 0Error Source= Microsoft VBScript runtime errorError 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

RE: DTS and Global Variables

Steve DiDomenico-197994 — Thu, 23 Feb 2006 06:44:00 GMT

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

RE: DTS and Global Variables

Steve DiDomenico-197994 — Wed, 22 Feb 2006 12:05:00 GMT

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.bakTestFile2.bakTestFile3.bakTestFile4.bakNorthWind1.bakNorthWind2.bakNorthWind3.bak

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

TestFile1.bakNorthWind1.bakNorthWind2.bakNorthWind3.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 = .bakFiles 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

RE: DTS and Global Variables

Alex-217289 — Wed, 22 Feb 2006 09:15:00 GMT

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_SuccessEnd FunctionHere 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 /EHere's an extract from the log file:Logging File Purge Activity: 2/15/2006 10:13:04 AM----------------------------------------Path = C:\Customers\AmericanAirlines\ImportBackup\Mask = .bakFiles 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

RE: DTS and Global Variables

Steve DiDomenico-197994 — Wed, 22 Feb 2006 08:58:00 GMT

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

RE: DTS and Global Variables

Grant Sutcliffe — Tue, 21 Feb 2006 09:22:00 GMT

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

RE: DTS and Global Variables

Andy Brons — Mon, 20 Feb 2006 07:07:00 GMT

Great article.

DTS and Global Variables

Alex-217289 — Tue, 31 Jan 2006 18:39:00 GMT

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