Delete files that are older than 7 days

  • I have a folder in d drive called Archive (d:\archive) which stores all data load files. The name of these files various, and I need to create a ssis job to delete all these archived load files older than 7 day.

    How I can do that?

    Thanks,

    Q

  • My personnal favorite is to put the file date in the filename. IE : ETL_ABC_20110429.

    Then simply use xp_cmdshell to load the file names. Parse the date. Start a cursor with the list of files to delete and execute.

    There are other options but they have had bugs over the years and this has always worked for me so I stuck with it.

  • Thanks.

    These are load files of various sort and do not come with date extension.

    Q

  • Can you ask to have those file names sent with timestamp? It's really not that much work. Also it makes it easier to understand what's in the file.

  • QQ-485619 (4/29/2011)


    I have a folder in d drive called Archive (d:\archive) which stores all data load files. The name of these files various, and I need to create a ssis job to delete all these archived load files older than 7 day.

    How I can do that?

    Thanks,

    Q

    Here is a powershell script I have scheduled daily that removes old files:

    $Now = Get-Date

    $Days = “10”

    $TargetFolder = “C:\Database_Backups”

    $LastWrite = $Now.AddDays(-$days)

    $Files = get-childitem $TargetFolder -include *.zip -recurse | Where {$_.LastWriteTime -le “$LastWrite”}

    foreach ($File in $Files)

    {if (!($File -eq $null)) {remove-item $File | out-null} }

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • You can use a script task in SSIS and use something like the following:

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic

    ' The ScriptMain class is the entry point of the Script Task.

    Imports System

    Imports System.IO

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    Public Sub Main()

    Dim SourcePath As String

    Dim PurgeDays As Integer

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

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

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

    For Each file As FileInfo In dir.GetFiles()

    If (Now - file.LastWriteTime).Days > PurgeDays Then file.Delete()

    Next

    Next

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class

    In the above, I have 2 variables defined. The PurgeDays variable contains the number of days to keep files and the SourcePath variable contains the directory where the files are.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • You could use a maintenance plan if you know the file extensions... Is that cheating ?

  • The script task is more of something that I am looking for. However, I got an error when I am testing it:

    Error 30002: Type 'DirectoryInfo' is not defined.

    Line 23 Column 25 through 37

    Can you guide me to fix it?

    Thanks

  • QQ-485619 (4/29/2011)


    I have a folder in d drive called Archive (d:\archive) which stores all data load files. The name of these files various, and I need to create a ssis job to delete all these archived load files older than 7 day.

    How I can do that?

    Thanks,

    Q

    If you must do this purely with SSIS then a Script Task that loops over the directory, checks the file info of each file and conditionally deletes it is one option. You could offload the file enumeration to a ForEach Loop container and only use a Script Task to get the file info and conditionally do the delete.

    If you are not restricted to using SSIS then I would second what toddasd said...use PowerShell! It's much simpler than what I just described above on how to do the task with SSIS, does not require enabling xp_CmdShell and can be scheduled and executed in any number of contexts.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • QQ-485619 (4/29/2011)


    The script task is more of something that I am looking for. However, I got an error when I am testing it:

    Error 30002: Type 'DirectoryInfo' is not defined.

    Line 23 Column 25 through 37

    Can you guide me to fix it?

    Thanks

    Not sure - what did you change with the script I provided? That script came from a working purge task I have in an SSIS package. My guess is that it is failing because your SourcePath information is not valid.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Did you declare the property for "DirectoryInfo" somewhere in your package?

    Thanks,

  • Hi,

    When testing out this code I keep getting an error underneath all the "Dts." with the error saying "reference to a non-shared object requires an object reference", is there something that needs to be added to this code to remove this error? Thanks in advance.

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic 2008.

    ' The ScriptMain is the entry point class of the script.

    Imports System

    Imports System.IO

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Tasks

    Imports Microsoft.SqlServer.Dts.Runtime

    Enum ScriptResults

    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

    Public Class ScriptMain

    Public Sub Main()

    Dim SourcePath As String

    Dim PurgeDays As Integer

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

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

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

    For Each file As FileInfo In dir.GetFiles()

    If (Now - file.LastWriteTime).Days > PurgeDays Then file.Delete()

    Next

    Next

    Dts.TaskResult = ScriptResults.Success

    End Sub

    End Class

  • Using strictly ssis tasks, for each file enumerator, script task to get file info,

    conditionally delete file, done.

    Works great if the directory is not empty, barfs a warning, then fails after 30 messages.

    I'd like to not get the warnings, how to ?

  • rsampson (10/6/2016)


    Using strictly ssis tasks, for each file enumerator, script task to get file info,

    conditionally delete file, done.

    Works great if the directory is not empty, barfs a warning, then fails after 30 messages.

    I'd like to not get the warnings, how to ?

    I realize this thread is old but the post above necro'd it. 🙂

    If you're trying to delete all of a particular file type (or all files) prior to a certain date, why not avoid having to write loops, write your own conditional tests, and etc, etc? Call a command task that uses FORFILES. You can use a single line of code to delete all files with a certain extension in the "current" directory and all sub-directories.

    Here's an example of listing the "Modified Date" and the "FULL PATH" of all files with a mask of "*.txt" the live in the "C:\Temp" directory and all of its sub-directories (/S is for recursion of all sub directories) that have a "Modified Date" date that occurred prior to 01/01/2016 (mm/dd/yyyy - no other format is possible).

    FORFILES /P "C:\Temp" /M *.txt /S /D -01/01/2016 /C "CMD /C ECHO @FDate @Path"

    Change everything to the right of "CMD /C" to the delete command that you want (probably DEL @Path) and you're done.

    For more information, please see the following two links...

    [font="Arial Black"]FORFILES command[/font]

    [font="Arial Black"]DEL command[/font]

    Personally, I avoid SSIS and PowerShell for things like this. I hate writing loops. Let the machine write its own pseudo-cursors for you, just like in T-SQL set based code.

    As a bit of a sidebar, file deletions are mostly permanent and you can make some terrible mistakes. My recommendation would be to have one command to move the files to an "intended delete" area (RoboCopy works well as a separate command not using FORFILES) and only delete from that area after a given period of time. That will give people the opportunity to miss their files and bug you to put them back, which you'll easily be able to do because you will have them.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • else,

    i used this syntax

    DECLARE @CMD sysname

    --create .bat:

    /*.bat:

    @echo off

    forfiles /p "d:\backup\bck_denyc\combine" /m *.bak /c "cmd /c del /Q @file" /d -30 --to delete 30 days back

    @echo off

    */

    --execute the .bat:

    exec @cmd = xp_CMDShell 'd:\backup\bck_Denyc\"delete folder".bat'

    If (@CMD = 1)

    PRINT 'SUCCESS'

    ELSE

    PRINT 'FAILURE'

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply