discovering files in the FileSystem, through SSIS

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

--r开发者_StackOverflow社区emove 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'

Use the ForEach loop with the file enumerator.

Since you're only inserting file names in a table (i.e. not doing any processing on each file at the same time in SSIS), I suggest doing it all with .NET in a script task. This will also make it easy to add additional logic, such as filtering names etc. See the following items in System.Data.SqlClient:

SqlConnection
SqlCommand
SqlCommand.Parameters
SqlCommand.ExecuteNonQuery()