November 10, 2009 at 8:00 am
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
November 10, 2009 at 8:24 am
use RESTORE HEADER command to automate.
-Vikas Bindra
November 10, 2009 at 9:09 am
explain please
November 10, 2009 at 9:38 am
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
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
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