How can I use data returned from a query to delete files on my network?

SSIS and Import/Export
1

Hi all

At my workplace, we use a SQL Server based application - Dynamics AX. Using this application, we create all our sales invoices. At the time of invoice creation, the system emails all our invoices to the customer when they are generated. This process also stores an archive copy on our file storage.

Our requirement has now changed to only store those invoices which are still outstanding and have not been paid. I have already come up with the TSQL that returns a list of all outstanding invoices - this list would look like the below:

InvoiceID
254425
223352
225854
225685
295568

etc

The invoice archive copies are stored with a filename that corresponds to the InvoiceID, eg for invoice 254425, the filename would be 254425.pdf.

How can I use the list of unpaid invoices returned from the SQL query, to delete all files from the archive location whose filename does not match one of the items in the SQL query results? Ideally, I would want this task to run by itself every week without user intervention, unless there was an error in which case I get an email to say the task couldnt run

This problem has been bugging me for a while, so any help would be most appreciated
Cheers

2 
DECLARE @i table
   (
      InvoiceID int
   );
INSERT @i VALUES (254425), (223352), (225854), (225685), (295568);
DECLARE @t table
   (
      file_name nvarchar(100)
    , depth     tinyint
    , is_file   bit
   );
INSERT @t EXEC sys.xp_dirtree 'C:\PathToTheFiles', 0, 1;
DECLARE c CURSOR FOR
   SELECT
      'DEL ' + t.file_name
   FROM
      @t t
   LEFT JOIN
   @i    i
      ON Cast(Left(t.file_name, 5) as int) = i.InvoiceID
   WHERE
      i.InvoiceID IS NULL;
DECLARE @cmd nvarchar(500);
OPEN c;
FETCH c
INTO
   @cmd;
WHILE @@Fetch_Status = 0
BEGIN
   EXEC sys.xp_cmdshell @cmd, no_output;
   FETCH c
   INTO
      @cmd;
END;