How to read a list of files in a directory

create table my_files ([filename] varchar(1000), depth int, file_flag int)

INSERT INTO my_files
EXEC xp_dirtree 'C:\MyDir',1,1

select [filename] from my_files
where file_flag = 1
and [filename] like '%.txt'

Typically this would then be used in a cursor to execute SQL commands using the files or filenames.
However, be aware that xp_dirtree is an undocument/unsupported interface, and might not be available in future releases of SQL Server.