[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:rolleyes:)

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 :)

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

Quote:

---

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.

Quote:

---

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.