SSIS 集合中的通配符{不包括}名称 xlsx
我有一个内置于 SSIS 的进程,它循环遍历 Excel 文件并仅从包含名称报告的文件中导入数据.
I have a process built in SSIS that loops through Excel files and Import data only from those that include name Report.
我用作表达式的 UserVariable 是:*Report*.xlsx
它工作得很好.现在我正在尝试构建类似的循环,但仅针对文件名中不包含 Report 的文件.
My UserVariable used as Expression is: *Report*.xlsx
and it works perfectly fine. Now I am trying to build similar loop but only for files that DOES NOT include Report in file name.
类似于*<>Report*.xlsx
有可能吗?
感谢您的帮助!
马特
推荐答案
遗憾的是,您无法使用 SSIS 表达式(类似 *[^...]*.xlsx
) 你必须寻找一些解决方法:
Unfortunately, you cannot achieve this using SSIS expression (something like *[^...]*.xlsx
) you have to search for some workarounds:
解决方法
第一
在进入 Loop 之前使用 Execute Script Task
获取 - 过滤的文件列表,然后使用 ForEach Loop 容器(Ado 枚举器)进行循环
Get List of - filtered - files using an Execute Script Task
before entering Loop and loop over then using ForEach Loop container (Ado enumerator)
- 您必须使用类型
System.Object
(范围:包) - 为每个循环容器添加
执行脚本任务
,并添加User::FilesList
作为ReadWrite Variable 在脚本中编写以下代码:
- You have to a a SSIS variable (ex:
User::FilesList
) with typeSystem.Object
(Scope: Package) - Add an
Execute Script Task
befor the for each Loop container and addUser::FilesList
as a ReadWrite Variable In the Script Write The following Code:
导入 System.Linq进口系统.IO导入 System.Collections.Generic
Imports System.Linq Imports System.IO Imports System.Collections.Generic
Public Sub Main()
Dim lstFiles As New List(Of String)
lstFiles.AddRange(Directory.GetFiles("C:Temp", "*.xlsx", SearchOption.TopDirectoryOnly).Where(Function(x) Not x.Contains("Report")).ToList)
Dts.Variables.Item("FilesList").Value = lstFiles
Dts.TaskResult = ScriptResults.Success
End Sub
在 For each Loop Container 中选择 Enumertaion Type 为 'From variable Enumerator' 并选择 FilesList
变量作为源
屏幕截图
第二
在for each循环内添加一个Expression Task
来检查文件是否包含Report
字符串
Inside the for each loop add an Expression Task
to check if the file contains Report
string or not
- 添加一个
System.Boolean
类型的变量 (名称:ExcludeFile) - 在 ForEach 循环容器内,在导入 Excel 文件的 DataFlowTask 之前添加一个
Expression Task
组件
- Add a variable of type
System.Boolean
(Name: ExcludeFile) - Inside the ForEach Loop Container add an
Expression Task
component before the DataFlowTask you that imports the Excel File
在表达式任务中写下以下内容:
Inside The Expression Task write the following:
@[User::ExcludeFile] = (FINDSTRING(@[User::XlsxFile], "Report", 1 ) == 0)
双击表达式任务和DataFlowTask之间的连接器,编写如下表达式
Double Click on the connector between the expression task and the DataFlowTask and write the following expression
@[User::ExcludeFile] == False
注意:没有必要使用 Expression Task
来验证这一点,您可以使用 Dummy DataFlowTask 或 Script Task> 检查文件名是否包含您要排除的关键字
Note: It is not necessary to use an Expression Task
to validate this you can use a Dummy DataFlowTask or a Script Task to check if the filename contains the Keyword you want to exclude or not
相关文章