discovering files in the FileSystem, through SSIS

Question

I have a folder where files are going to be dropped for importing into my data warehouse.

\\server\share\loading_area

I have the following (inherited) code that uses xp_cmdshell shivers to call out to the command shell to run the DIR command and insert the resulting filenames into a table in SQL Server.

I would like to 'go native' and reproduce this functionality in SSIS.

Thanks in advance guys and girls. Here's the code

USE MyDatabase
GO

declare @CMD varchar(500)
declare @EXTRACT_PATH varchar(255)

set @EXTRACT_PATH = '\\server\share\folder\'

create table tmp_FILELIST([FILENUM] int identity(1,1), [FNAME] varchar(100), [FILE_STATUS] varchar(20) NULL CONSTRAINT [DF_FILELIST_FILE_STATUS] DEFAULT ('PENDING'))
set @CMD = 'dir ' + @EXTRACT_PATH + '*.* /b /on'

insert tmp_FILELIST([FNAME])
exec master..xp_cmdshell @CMD

--remove the DOS reply when the folder is empty
delete tmp_FILELIST where [FNAME] is null or [FNAME] = 'File Not Found'
--Remove my administrative and default/common, files not for importing, such as readme.txt
delete tmp_FILELIST where [FNAME] is null or [FNAME] = 'readme.txt'

Answer 1

Use SSIS Execute Process Task. The Execute Process task can specify the command prompt arguments that the executable file or batch file requires.

Answer 2

Use the ForEach loop with the file enumerator.