dcsimg
Results 1 to 3 of 3

Thread: [RESOLVED] Use CmdExec to copy/delete files returned from a query

  1. #1

    Thread Starter
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,848

    Resolved [RESOLVED] Use CmdExec to copy/delete files returned from a query

    This question is for SQLServer 2005 running in 2000 compatibility mode (yep, we still have one)

    To comply with the new GDPR data retention rules we have a SQL Server agent job that purges old customer records. There are also a bunch of files in the file system (scanned letters, documents, that sort of thing) held in the file system and linked to from the database. I need to purge these when I purge the customer they belong to.

    Our purge process consists of moving customers into a parallel repository where it's held for 30 days before being deleted entirely (this just gives us a 30 day grace period during which we can spot that we're about to remove a customer who's still active) so I need to support both copy and remove operations against the file system.

    At present I've got a sql server agent job that simply runs some sql to handle the records and now I need to add the file handling. I have a sql query that will identify all the files I need to copy/delete on any given day (this will tend to be in the low thousands though it will vary to some extent). I've also done some googling and found how to copy/delete a single file using either CmdExec. Where I'm struggling is in using the result of the query to delete many files. Can anyone help me with that?



    Edit> I should add that I know I can do this quite simply by moving it into an SSIS package. That's going to be my fall-back solution but I'd rather keep the whole thing in the agent job to make it easier to manage and maintain in the future.



    Edit2> scratch that. We've gone with a different process
    Last edited by FunkyDexter; Aug 2nd, 2018 at 05:07 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,717

    Re: [RESOLVED] Use CmdExec to copy/delete files returned from a query

    I would have done the whole thing in an SSIS PAckage, then set it up as a SQL Agent job to run on a regular basis. That's what I've done in the past for clients. We've had clients that have had processes where they drop off files into a folder, then an SSIS package is scheduled to run at certain times, plucks the files, processes them, then puts them into an archive.

    I'm curious though, you said you went a different route - what was it?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,848

    Re: [RESOLVED] Use CmdExec to copy/delete files returned from a query

    I would have done the whole thing in an SSIS PAckage
    If I was starting from scratch I'd probably have done the same but all the data handling (which is waaaaaay bigger and more complex than I've portrayed here) was already in place using a pure sql agent job and I didn't really want to start breaking that out to a new package.

    Also, this client doesn't really have a proper (or any) development process for DB stuff so the source of the package would probably just have been saved off to some folder somewhere and promptly forgotten about... right up until they wanted some critical maintenance and couldn't remember where it was. Keeping it directly in the job would have been simpler.

    I'm curious though, you said you went a different route - what was it?
    We're going to maintain a table of "files to be deleted" which we populate, along with a delete date, at the point of copying the records into the parallel system. That will be trivial to add to the existing agent job (in fact, I've already done it). Then the app development team are going to develop a small service to sit on the file server, interrogate the table and delete the files at the appropriate time. Because they've got better practices in place I'm more confident that that wont go walkabout

    A bit of me would still like to have kept the whole lot under one roof but, the more we discussed it, the more we realised that the tasks don't really synch up with each other anyway (e.g. we realised we don't need a parallel structure for the documents because they'll be unreachable with the records anyway). I'm pretty sanguine with where we've ended up.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width