通过 SSIS 发现文件系统中的文件
我有一个文件夹，其中的文件将被删除以导入到我的数据仓库中。
\\server\share\loading_area
我有以下（继承的）代码，它使用 xp_cmdshell shivers 调用命令 shell 来运行 DIR 命令并将生成的文件名插入 SQL Server 中的表中。
我想“原生”并在 SSIS 中重现此功能。
预先感谢伙计们和女孩们。这是代码
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'
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'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
由于您只是在表中插入文件名（即不在 SSIS 中同时对每个文件进行任何处理），因此我建议在脚本任务中使用 .NET 来完成这一切。这也将使添加额外的逻辑变得容易，例如过滤名称等。请参阅 System.Data.SqlClient 中的以下项目：
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:
将 ForEach 循环与文件枚举器结合使用。
Use the ForEach loop with the file enumerator.