SSIS Delete Files older than 30 days

Question

I am attempting to use a VB.Net script in SSIS that will delete all files from an archive folder that are older than 30 days (based on creation date) using a VB.Net script. This is the script:

Imports System
Imports System.Data
Imports System.IO
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()>
<System.CLSCompliantAttribute(False)>
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()

        Dim FilePath As String

        Dim Days As Integer

        Days = CInt(Dts.Variables("User::Days").Value)

        FilePath = CStr(Dts.Variables("User::FilePath").Value)

        For Each dir As DirectoryInfo In New DirectoryInfo(FilePath).GetDirectories()

            For Each file As FileInfo In dir.GetFiles()

                If (Now - file.CreationTime).Days > Days Then file.Delete()

            Next

        Next

    End Sub

    End Class

Where the FilePath variable (String) = ArchiveFolder file path and Days(INT32) = 30

They have both been set as ReadOnlyVariables.

The script task executes in SSIS "successfully" but is not actually deleting the files.

Any clue why?

Perhaps Now - file.CreationTime is around the wrong way. If yuo set a breakpoint and debug it, it might become clear what the issue is — Nick.McDermaid

salli salli · Accepted Answer · 2020-01-27T20:28:53

Try this

  Dim Days = CInt(Dts.Variables("User::Days").Value) * -1

        Dim directory As DirectoryInfo = New DirectoryInfo(FilePath)

        For Each file In directory.GetFiles("*", SearchOption.AllDirectories)
            If (Now.AddDays(Days) <= file.CreationTime) Then file.Delete()
        Next

SSIS Delete Files older than 30 days

1 Answers