SSIS 集合中的通配符{不包括}名称 xlsx

2021-12-30 00:00:00 etl sql-server-2008 sql-server ssis ssis-2008

我有一个内置于 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)

  1. 您必须使用类型 System.Object (范围:包)
  2. 为每个循环容器添加执行脚本任务，并添加User::FilesList作为ReadWrite Variable

  3. 在脚本中编写以下代码:

  1. You have to a a SSIS variable (ex: User::FilesList) with type System.Object (Scope: Package)
  2. Add an Execute Script Task befor the for each Loop container and add User::FilesList as a ReadWrite Variable

  3. 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

    1. 添加一个 System.Boolean 类型的变量 (名称:ExcludeFile)
    2. 在 ForEach 循环容器内，在导入 Excel 文件的 DataFlowTask 之前添加一个 Expression Task 组件
    1. Add a variable of type System.Boolean (Name: ExcludeFile)
    2. Inside the ForEach Loop Container add an Expression Task component before the DataFlowTask you that imports the Excel File

    1. 在表达式任务中写下以下内容:

    1. Inside The Expression Task write the following:

     @[User::ExcludeFile]  = (FINDSTRING(@[User::XlsxFile], "Report", 1 ) == 0)

    1. 双击表达式任务和DataFlowTask之间的连接器，编写如下表达式

    1. 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

    相关文章