Post reply

SSIS to archive backup files

  • November 10, 2009 at 8:00 am

    #136120

    I am wanting to automate my archive process of my backup files. I want to use SSIS package to check the file date, if it is older than set criteria move it to a network share designated for archiving of backup files. Also think would want a step that if archive is older than set criteria to delete it from archive folder. Has anybody done something like this ? Part I am wondering is checking the date of the backup file, I figure this needs to be done in a script task.

    Any suggestions appreciated

    Thanks

    Mark Copley

    macopley@hotmail.com

  • November 10, 2009 at 8:24 am

    #1077302

    use RESTORE HEADER command to automate.

    -Vikas Bindra

  • November 10, 2009 at 9:09 am

    #1077335

    explain please

  • November 10, 2009 at 9:38 am

    #1077360

    Mark,

    Assuming you want to archive the file based on the date stamp of the file (rather than the header data of the backup itself), you can use a script task to interrogate each file along with a filesystem task to move the file. If you want to run this process iteratively (i.e., process several files at once), wrap both of these into a For Each loop using the file enumerator.

    First, set up a True/False variable to store the result of your file timestamp check. Next, within your script task, use the System.IO.File.GetLastWriteTime to test whether the file timestamp meets the archive criteria, and set the true/false variable to the result of that check. Configure your File System Task to move the current file to the archive folder, and connect the script task to the file system task using a precedence constraint expression that will only execute the File System Task if your boolean check variable is true.

    Let me know if this helps, or if you need some additional assistance.

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • November 10, 2009 at 11:49 am

    #1077418

    Tim -

    That makes sense , I was missing the System.IO.File.GetLastWriteTime to the file timestamp.

    Thanks I will give that a shot and let you know.

    Mark

  • November 11, 2009 at 12:12 am

    #1077654

    within your script task, use the System.IO.File.GetLastWriteTime to test

    Yes! This looks better that reading the header. Thanks Mark!

    -Vikas Bindra

Viewing 6 posts - 1 through 5 (of 5 total)

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