discovering files in the FileSystem, through SSIS
- by cometbill
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'
