Post reply

How To Delete Files based on some condition (like age) using SQL Server

  • June 9, 2009 at 6:07 pm

    #222264

    I'm looking for feedback on methods to purge what are old temp files for a directory using any of the parts that come with SQL Server from T-SQL to SSIS. Mainly Iid like to go with whatever is the easiet to implement & maintain and is flexable enought to accept criteria so that it doesn't delete every file in a specified directory but just those meeting some condition like age.

    We will be starting with the deletion fo all files with a Creation date greater then X days however this could expand to more complex rules on file deletion so I'd like flexability. I would imagine that this is exactly what SSIS is best at but I am not that versed in SSIS and when I looked at it to try and figure this out I found it to be very over whelming. From what i could tell from the BOL Help is that it looks like with SSIS you have to do like you do in C++ programing for any action include the simple and that is create & setup every action & object no matter how implied some may be. You get great flexability & options but at the price of great complexity.

    I'm not however totally oppossed to SSIS if there is a decent How-To artcile on this.

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • June 9, 2009 at 8:35 pm

    #1007162

    YSLGuru (6/9/2009)

    Letting a programmer make database design decissions is like letting a politician manage your checkbook; a very bad idea that often comes with disasterous results that may not show up till it's too late to fix.

    Nice. 🙂

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 10, 2009 at 7:29 am

    #1007379

    Actually, based on what you describe, I'm not sure I'd use SSIS to do the job. I think I'd drop out to PowerShell to manage this. If you do it in SSIS, with the simple criteria, it'll work fine, but if you need to go to more complex criteria, you're just going to end up scripting it anyway. Why not start scripting it in a language that's a bit more suited.

    Fair warning. I'm still learning PowerShell so any help I provide in that area will have to be taken with a VERY large grain of salt.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 10, 2009 at 12:22 pm

    #1007612

    Grant Fritchey (6/10/2009)

    Actually, based on what you describe, I'm not sure I'd use SSIS to do the job. I think I'd drop out to PowerShell to manage this. If you do it in SSIS, with the simple criteria, it'll work fine, but if you need to go to more complex criteria, you're just going to end up scripting it anyway. Why not start scripting it in a language that's a bit more suited.

    Fair warning. I'm still learning PowerShell so any help I provide in that area will have to be taken with a VERY large grain of salt.

    Thanks for the input but after spending a couple of hours (yet again) on Powershell, persuing the Microsoft PS specific sites & Googleing on PowerShell for sample code on how to do this I am once again (this is probably the 4th or 5th time this has happened) reminded why I hate, no, DESPISE Powershell and why looking at it for anything is a waste of time and a headache generator.

    Powershell has got to be the most non-intuitive, convoluted backwards thinking technology to come along this century. It looks like Old School Unix Programmers & Pre-monitor day punch cards got together to make a baby scripting lanaguage/technology and had to settle instead for what we now call Powershell.

    Here's how they decided on the name:

    Power = The great power needed to comprehend even the basics of how to use this tech.

    Shell = the impenetrable shell of understanding one must pierce to use this tech

    I actually had high hopes for Powersheel the first time & the second time and even the third time I took a stab at trying to work with and use it and each time it ends in frustration. In another 4- 6 months I will probably revist this again in hopes that this beast has been cleaned up and tamed and comes with a more simplified and or easier to understand interface, something with an easier learning curve more along the lines of C++ programming or better because that would certainly be easier then what we have now.

    Thanks Again

    Kindest Regards,

    Just say No to Facebook!

  • June 10, 2009 at 12:23 pm

    #1007613

    WayneS (6/9/2009)

    YSLGuru (6/9/2009)

    Letting a programmer make database design decissions is like letting a politician manage your checkbook; a very bad idea that often comes with disasterous results that may not show up till it's too late to fix.

    Nice. 🙂

    Thanks Wayne. I've had to deal (in extensive qunatities) with product prodcued by developer turned DBA and it can definately leave you with a bad taste.

    Kindest Regards,

    Just say No to Facebook!

  • June 10, 2009 at 1:27 pm

    #1007647

    YSLGuru (6/10/2009)

    Thanks for the input but after spending a couple of hours (yet again) on Powershell, persuing the Microsoft PS specific sites & Googleing on PowerShell for sample code on how to do this I am once again (this is probably the 4th or 5th time this has happened) reminded why I hate, no, DESPISE Powershell and why looking at it for anything is a waste of time and a headache generator.

    Powershell has got to be the most non-intuitive, convoluted backwards thinking technology to come along this century. It looks like Old School Unix Programmers & Pre-monitor day punch cards got together to make a baby scripting lanaguage/technology and had to settle instead for what we now call Powershell.

    Here's how they decided on the name:

    Power = The great power needed to comprehend even the basics of how to use this tech.

    Shell = the impenetrable shell of understanding one must pierce to use this tech

    I actually had high hopes for Powersheel the first time & the second time and even the third time I took a stab at trying to work with and use it and each time it ends in frustration. In another 4- 6 months I will probably revist this again in hopes that this beast has been cleaned up and tamed and comes with a more simplified and or easier to understand interface, something with an easier learning curve more along the lines of C++ programming or better because that would certainly be easier then what we have now.

    Thanks Again

    But what do you really think about PowerShell?

    I do hear you. I've been dabbling with it and reason it's still dabbling is because it seems so damned obtuse.

    Regardless, to get sophisticated on the problem you outlined you will need to move to some type of programming, even if it's within SSIS. Simple date math for files can be done in SSIS without scripting, but after that...

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 18, 2009 at 1:23 pm

    #1012012

    Grant Fritchey (6/10/2009)

    YSLGuru (6/10/2009)

    Thanks for the input but after spending a couple of hours (yet again) on Powershell, persuing the Microsoft PS specific sites & Googleing on PowerShell for sample code on how to do this I am once again (this is probably the 4th or 5th time this has happened) reminded why I hate, no, DESPISE Powershell and why looking at it for anything is a waste of time and a headache generator.

    Powershell has got to be the most non-intuitive, convoluted backwards thinking technology to come along this century. It looks like Old School Unix Programmers & Pre-monitor day punch cards got together to make a baby scripting lanaguage/technology and had to settle instead for what we now call Powershell.

    Here's how they decided on the name:

    Power = The great power needed to comprehend even the basics of how to use this tech.

    Shell = the impenetrable shell of understanding one must pierce to use this tech

    I actually had high hopes for Powersheel the first time & the second time and even the third time I took a stab at trying to work with and use it and each time it ends in frustration. In another 4- 6 months I will probably revist this again in hopes that this beast has been cleaned up and tamed and comes with a more simplified and or easier to understand interface, something with an easier learning curve more along the lines of C++ programming or better because that would certainly be easier then what we have now.

    Thanks Again

    But what do you really think about PowerShell?

    I do hear you. I've been dabbling with it and reason it's still dabbling is because it seems so damned obtuse.

    Regardless, to get sophisticated on the problem you outlined you will need to move to some type of programming, even if it's within SSIS. Simple date math for files can be done in SSIS without scripting, but after that...

    I would like to use Powershell but everytime I take an hour to try and look into this tech I ended up wasting half a day or more and am no better off then I was before I started. We have a former Unix guy working in our office who said he likes Powershell because it reminds him of Unix. Proof positive that Powershell is a secret conspiracy to rid the world of easy to undertsand/use programing code.

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • June 19, 2009 at 4:11 am

    #1012258

    I'd write (aka download from google) a vb script to do the job then either use Windows scheduled task widget to run it or create a DOS batch file which SQL can call.

    This avoids the nasty and unnecessary headache of trying to do anything even remotely complex with the powershell commands.

  • June 19, 2009 at 4:37 am

    #1012268

    If you don't like the idea of SSIS or Powershell for this task, you could use good old fashioned xp_cmdshell.

    It's a bit clunky, but it's possible to load the contents of a "Dir" command into a table using xp_cmdshell, you can then write some TSQL to generate the "Del" command to delete the ones you don't need.

  • June 19, 2009 at 11:21 am

    #1012574

    FNS (6/19/2009)

    I'd write (aka download from google) a vb script to do the job then either use Windows scheduled task widget to run it or create a DOS batch file which SQL can call.

    This avoids the nasty and unnecessary headache of trying to do anything even remotely complex with the powershell commands.

    Good idea but you'd be suprised how not easy that is. I tried searching and perhaps I just did not use the right combination of criteria but I had trouble finding sometihng. I'm seriously considering putting togther a VB based applet/service to do this as that would end taking less time that what it looks like I'm gonna spend trying to find a way to do this with scripting.

    Thanks

    Kindest Regards,

    Just say No to Facebook!

  • June 19, 2009 at 11:22 am

    #1012575

    Ian Scarlett (6/19/2009)

    If you don't like the idea of SSIS or Powershell for this task, you could use good old fashioned xp_cmdshell.

    It's a bit clunky, but it's possible to load the contents of a "Dir" command into a table using xp_cmdshell, you can then write some TSQL to generate the "Del" command to delete the ones you don't need.

    Thanks for the suggestion.

    Kindest Regards,

    Just say No to Facebook!

Viewing 11 posts - 1 through 10 (of 10 total)

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