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

[FunkyDexter]

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