Since “SSIS Nuggets” is a registered trademark ;-) by Jamie (http://blogs.conchango.com/jamiethomson) I had to invent my own brand of small SSIS samples you are invited to use in your own projects. I hope I’ll find some time to provide more snippets in the future…
So what do you get in this snippet: this is an example how to delete old files (created a given time ago) in a directory. My intension to build this snippet was that I didn’t find any way to “age out” backup files. In SQL Server 2000 you could define that backup files are deleted after i.e. 2 weeks. I’m not aware of any equivalent in SQL Server 2005 (perhaps you know how?!). Other uses of this snippet include deleting old log files or whatever you imagine…
This package has a “ForEach Loop” which looks for files in a directory (you define the path and the wildcard for the files to find in this component). When it finds one or more matching files a script task calculates the age of the file by reading out the creation date and comparing it with the actual date. The result is stored in a variable. Based on this variable a “Filesytem Task” is called (or not). This is done by an expression based constraint. So you can define how old the files to be deleted should be by changing the expression in the properties of the link between the script and the Filesystem task.
You can download this SSIS snippet here: http://tpagel.gmxhome.de/snippets/deleteoldfiles.zip
Comments are always appreciated…
Thomas