"Delete files older than X days" error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » "Delete files older than X days" error

"Delete files older than X days" error

Rate Topic

Display Mode

Topic Options

Author

Message

marees.inspire

Posted Monday, December 27, 2010 10:20 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 2:44 PM
Points: 60, Visits: 237

Hi,

I am using the script task to delete the old files(older than 10 days) present in a particular folder. (D:\FIles)

Code to delete the old files:

Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld

'Archive Files
iDaysOld = CInt(Dts.Variables("User::DaysKeepArchive").Value)

'Alter the variable DaysKeepArchive as needed.
oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = CStr(Dts.Variables("User::ArchiveFiles").Value)

'Alter the variable ArchiveFiles as needed.
oFolder = oFSO.GetFolder(sDirectoryPath)
oFileCollection = oFolder.Files

'Walk through each file in this folder collection.
'If it is older than DaysKeepArchive days, then delete it.
For Each oFile In oFileCollection
If oFile.DateLastModified < (DateTime.Now.AddDays(-iDaysOld)) Then
oFile.Delete(True)
End If
Next

'Clean up
oFSO = Nothing
oFolder = Nothing
oFileCollection = Nothing
oFile = Nothing
Dts.TaskResult = Dts.Results.Success

But i am getting the following error when execute it :

Exception from HRESULT: 0x800A0046 (CTL_E_PERMISSIONDENIED)

at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at ScriptTask_c546ddcc3bdc4c85a39194f9edce9ca6.ScriptMain.Main() in dts://Scripts/ScriptTask_c546ddcc3bdc4c85a39194f9edce9ca6/ScriptMain:line 41

Can somebody help on this.

THANKS in Advance.

Post #1039629

Marco V

Posted Tuesday, December 28, 2010 4:09 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 13, 2013 2:14 AM
Points: 462, Visits: 503

maybe this is shorter and fills in you needs: the maintenance cleanup task

you can even script it http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx

Post #1039692