locked
Delete files older than 3 days using SSIS tasks RRS feed

  • Question

  • I want to delete all files in a given folder that are older than 3 days from today's date.

    I tried using a "Foreach Loop container"  with a "File System" task inside it but found I couldn't access any file properties such as the file creation date. Am I using the wrong task for this job?

     

    TIA,

     

    Barkingdog

     

    P.S. History of the problem:

    I used the sql 2005 Database maintenance program to setup our database backup jobs. One day I noticed that the free space on the drive where we keep the dumps had grown small. I found that we had at least 4 weeks of dumps in there, not the 3 days I wanted to keep!  I looked but could not find the "delete file" option in the SSIS package generated by the Database Maintenance Wizard. No wonder the files were piling up.

    Friday, August 11, 2006 3:13 AM

Answers

  • Good idea. For now I just used this script in a Script task

     

             ...      

            Dim fi As System.IO.FileSystemInfo
            fi = My.Computer.FileSystem.GetFileInfo("c:\temp.txt")

            If (Now.DayOfYear - fi.CreationTime.DayOfYear) > 3 Then
                'fi.Delete()
            End If

    Tuesday, August 15, 2006 2:45 PM

All replies

  • if i'm not mistaken, you need to use either a script task or custom task...
    Friday, August 11, 2006 7:17 AM
  • Yes, that's what I finally did, thank you. BUT I am surprised that this feature -- which was in sql 2000, is apparently missing from the Mantenance plan in sql 2005.  This is a ridiculous shortcoming, unless one likes old backups to accumulate and take  all free disk space!.

     

    Yelp, yelp, yelp ............  ;)

    Barkingdog

     

     

     

    Monday, August 14, 2006 2:26 AM
  • If you need a feature then request it at Microsoft Connect.

    -Jamie

     

    Monday, August 14, 2006 8:46 AM
  • I will.

     

    Barkingdog

    Monday, August 14, 2006 6:58 PM
  • Would be a great addition to the file enumerator. Basically provide for more robust and flexible enumeration by date, file attributes etc. I don't think I'm the only one to have thought of this. Certainly useful.

    Kirk Haselden
    Author "SQL Server Integration Services"

    Tuesday, August 15, 2006 5:55 AM
  • Good idea. For now I just used this script in a Script task

     

             ...      

            Dim fi As System.IO.FileSystemInfo
            fi = My.Computer.FileSystem.GetFileInfo("c:\temp.txt")

            If (Now.DayOfYear - fi.CreationTime.DayOfYear) > 3 Then
                'fi.Delete()
            End If

    Tuesday, August 15, 2006 2:45 PM
  •  KirkHaselden wrote:

    Would be a great addition to the file enumerator. Basically provide for more robust and flexible enumeration by date, file attributes etc. I don't think I'm the only one to have thought of this. Certainly useful.

    Kirk Haselden
    Author "SQL Server Integration Services"

     

    Way ahead of you ;)

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=179264

    -Jamie

     

    Tuesday, August 15, 2006 3:11 PM
  • You da man!
    Tuesday, August 15, 2006 5:44 PM
  •  KirkHaselden wrote:
    You da man!

     

    Did you vote?

    -J

     

    Tuesday, August 15, 2006 9:44 PM
  • Hi,

    I'm not sure if you found out the answer to your question, nor if this is what you are looking for, however, when creating a maintanance plan, you can use a "Maintenance Cleanup Task" to delete files which are older than X days old.

    The trick to this is to make sure you've ticked the correct boxes.

    For instance, I setup my backups to backup to G:\SQLBackups and tick the "Create A Subdirectory for each database" option.  Therefore, when I put in a corresponding "Maintenance Cleanup Task", I make sure that I enter G:\SQLBackups in the "Folder" box, but also tick the "INCLUDE First Level Subfolders" option.  (This caught me out once as I couldnt understand why, even with the task in place, it wasnt deleting...)

    This definately works for SQL backups, and also SQL transaction logs (just change the file extension to trn, not bak)

    I'm not sure if you want to delete different files that aren't SQL backups but it MIGHT be possible to simply enter a different file extension in there to delete other types of files...

    Regards


    Andy

    Friday, October 8, 2010 11:48 AM
  • public void Main()
            {
                string directoryPath = @"\\DirectoryPath";
                string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.csv");
                foreach (string currFile in oldFiles)
                {
                    FileInfo currFileInfo = new FileInfo(currFile);
                    if (currFileInfo.CreationTime < DateTime.Now.AddDays(-1))
                    {
                        currFileInfo.Delete();
                    }
                }
                // TODO: Add your code here
                Dts.TaskResult = (int)ScriptResults.Success;
            }
    • Proposed as answer by sql learner12 Monday, April 30, 2012 6:51 PM
    Tuesday, May 24, 2011 12:53 PM
  • Here is an other example with a Script Task:
    http://microsoft-ssis.blogspot.com/2011/01/use-filedates-in-ssis.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    • Proposed as answer by sql learner12 Monday, April 30, 2012 6:51 PM
    Tuesday, May 24, 2011 1:06 PM
  • Hello All,

    I'm trying to do something similar, but how would you go about using this script if there is more than one file in the directory that needs to be deleted, based on how old it is? Is there a way to specify both files in the path? Any guidance would be appreciated.

    Thanks!

    Wednesday, August 24, 2011 5:06 PM