SSIS集合中的通配符{不包括}名称xlsx [英] WildCards in SSIS Collection {not include} name xlsx

问题描述

我有一个内置于SSIS中的过程，该过程循环遍历Excel文件并仅从名称为 Report 的文件中导入数据.

用作表达式的我的UserVariable是:*Report*.xlsx 而且效果很好.现在，我正在尝试建立类似的循环，但仅适用于文件名中不包含 Report 的文件.

类似于*<>Report*.xlsx

有可能吗?

感谢帮助！

马特

解决方案

不幸的是，您不能使用SSIS表达式(类似于*[^...]*.xlsx 的东西)来实现此目的，而必须寻找一些解决方法:

解决方法

第一

在进入循环并使用ForEach循环容器(Ado枚举器)之前使用Execute Script Task获取-过滤后的文件的列表

1. 您必须具有类型为System.Object (范围:包)
的SSIS变量(例如:User::FilesList)
2. 为每个Loop容器添加Execute Script Task，然后将User::FilesList添加为 ReadWrite变量

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

   导入System.Linq 导入System.IO 导入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
   

4. 在对于每个循环容器"中，将枚举类型选择为从变量枚举器"，然后选择FilesList变量作为源

ScreenShots

第二

在每个循环的内部添加Expression Task来检查文件是否包含Report字符串

1. 添加类型为System.Boolean (名称:ExcludeFile)
的变量
2. 在ForEach循环容器内，在导入Excel文件的DataFlowTask之前添加一个Expression Task组件

3. 在Expression Task的内部编写以下内容:

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

4. 双击表达式任务和DataFlowTask之间的连接器，然后编写以下表达式

   @[User::ExcludeFile] == False
   

注意:不必使用Expression Task进行验证，您可以使用 Dummy DataFlowTask 或 Script Task 来检查文件名包含您要排除的关键字

I have a process built in SSIS that loops through Excel files and Import data only from those that include name 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.

Something like *<>Report*.xlsx

Is it possible?

Thanks for help!

Matt

解决方案

Unfortunately, you cannot achieve this using SSIS expression (something like *[^...]*.xlsx) you have to search for some workarounds:

Workarounds

First

Get List of - filtered - files using an Execute Script Task before entering Loop and loop over then using ForEach Loop container (Ado enumerator)

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:

   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
   

4. In the For each Loop Container Choose the Enumertaion Type as 'From variable Enumerator' and choose FilesList variable as a source

ScreenShots

Second

Inside the for each loop add an Expression Task to check if the file contains Report string or not

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

3. Inside The Expression Task write the following:

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

4. Double Click on the connector between the expression task and the DataFlowTask and write the following expression

   @[User::ExcludeFile] == False
   

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

这篇关于SSIS集合中的通配符{不包括}名称xlsx的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！