0

I have a simple Integration Service package which has one SQL Task as shown below;

  • WLSTAGE Bakup using Red Gate: Calls master..sqlbackup to backup database

Here is the SQL to run the actual backup

exec master..sqlbackup '-E -SQL "BACKUP DATABASE [WLSTAGE] TO DISK = ''\\Backups\<AUTO>'' WITH COMPRESSION = 1, INIT, MAILTO = ''<left blank intentionally>'', VERIFY"'

Since I have specified the backup file name as <AUTO>, I am wondering if there is a simple way to remove backups older than 2 days (if possible, without having to write any plumbing code to parse the file name).

alt text

Community
  • 1
dance2die
  • 2,011
  • 7
  • 32
  • 41

1 Answers1

1

I'm not sure what the AUTO statement does, but you are probably aware of the Maintenance Cleanup task. Does it not do what you need?

I like powershell for things like this as well.

(get-item -path "d:\perflogs\*" | where {$_.creationTime -lt ((get-date) - (new-timespan -days 30))} | where {$_.extension -eq ".blg"}) | remove-item
Sam
  • 2,020
  • 1
  • 16
  • 22
  • @Sam: Specifying `` creates a file name dynamically using parent's directory name and current date stamp - Back to the main point, is it possible to call powershell scripts within SSIS package as you have suggested? – dance2die Jan 20 '10 at 18:07
  • I suppose it would be a command line call you could put in a job step. Did you try the Maintenance Cleanup task? – Sam Jan 22 '10 at 00:23
  • Thanks Sam, I have decided to use PowerShell in `Execute Process Task` mentioned in this StackOverflow answer - http://stackoverflow.com/questions/1070050/powershell-in-ssis/1070088#1070088 – dance2die Jan 29 '10 at 14:19
  • Glad you got it sorted out. I might consider a .net script task solution as well, to keep all the code in one place. I think you can run powershell from inside .net as well, so the .net would just be a bit of a wrapper. – Sam Feb 01 '10 at 17:17