SQL : Read PDF file names from folder and store the file names in table

• kran

  Old Hand

  Points: 338

  More actions

  March 19, 2019 at 8:10 am

  #416885

  Hi - An SSRS subscriptions create PDF files on a daily basis and drops them into the folder. Each file has a timestamp at the end of the filename. how do I write a SQL query to read these filenames from this folder with the today's date? For example, Today is March 18, 2019, and the query should pick up on today's file names. Thanks for your help. 
  
  

• adaddio

  Mr or Mrs. 500

  Points: 539

  More actions

  March 19, 2019 at 1:47 pm

  #2024942

  Maybe something like this will help. It will populate that temp table with the directory depth of the file/folder and a flag to indicate if the record is a file or a folder. You should be able to just adjust this to look at whatever directory you need the file listing from.

  IF OBJECT_ID('tempdb..##TempDirectory') IS NOT NULL DROP TABLE ##TempDirectory;
--Create a table to hold the file names in the directory we care about
Create Table ##TempDirectory (
                            Subsubdirectory    varchar(300)
                            ,Depth    int
                            ,[File] int
                            );
--Insert the files names into the table using command shell
insert into ##TempDirectory
EXEC xp_dirtree 'C:\Users\', 10, 1;

  select * from ##TempDirectory