每个文件枚举器中的SSIS文件筛选器 [英] SSIS File Filter in For Each File Enumerator

问题描述

我有一个For Each文件枚举器.在文件夹下，我有 E:\ Input

在文件中，我有"*" + @ [User:Date] +"*.*"

日期变量设置为 20180101 .

变量映射设置为 User :: FileToBeMoved

在枚举器中，我有一个文件系统移动文件，其源是变量 User :: FileToBeMoved ，目标是包含 E:\ Input \ Archive 的变量

我里面有文件，例如 In20180101.txt ， Intermediate20180101.txt 和 Out20180101.txt

文件未移动.似乎无法识别过滤器"*" + @ [User:Date] +"*.*"

如果我进入Windows资源管理器并将 * 20180101 *.* 放在搜索"框中，它将完全满足我的要求，向我显示了包含 20180101 的3个文件>

有人可以告诉我为什么这在SSIS中不起作用吗?

谢谢

迪克

解决方案

文件"部分提供了通用

您需要在 FileSpec

中指定表达式

我发现创建一个执行逻辑的变量比将其嵌入到容器中更容易，因为您可以检查变量中的值，而不是Tasks中的值.

使用BIML解决方案

如果您喜欢Biml，这将创建一个程序包，该程序包具有基于变量DateString的值将表达式应用于每个文件枚举器"的文件规范的所有工作.

 < Biml xmlns ="http://schemas.varigence.com/biml.xsd"><包裹><包裹名称="so_48067171"><变量>< Variable Name =日期" DataType ="String"> 20180101</Variable><变量名称="DateString" DataType ="String" EvaluateAsExpression ="true">"*" + @ [User :: Date] +"*"</Variable>< Variable Name ="CurrentFile" DataType ="String"> Demo.txt</Variable>< Variable Name ="ArchiveFolder" DataType ="String"> C:\ ssisdata \ archive</Variable></变量><任务>< ForEachFileLoop名称="FELC TodayFiles" Folder ="C:\ ssisdata \ input" FileSpecification ="*.txt">< VariableMappings>< VariableMapping Name ="0" VariableName ="User.CurrentFile"/></VariableMappings><表达式>< Expression ExternalProperty ="FileSpec"> @ [User :: DateString]</Expression></表达式><任务>< FileSystem Name ="FST存档文件" Operation ="MoveFile">< VariableInput VariableName ="User.CurrentFile"/>< VariableOutput VariableName ="User.ArchiveFolder"/></FileSystem></任务></ForEachFileLoop></任务></包装></Packages></Biml> 

您的待办事项是根据当前日期或日期减去1来设置@ [User :: Date]的值.

I have a For each file Enumerator. Under the folder I have E:\Input

Under the Files I have "*" + @[User:Date] + "*.*"

The date variable is set to 20180101.

The Variable Mappings are set to User::FileToBeMoved

Within the Enumerator I have a File System Move File with the Source being the Variable User::FileToBeMoved and the destination being a variable containing E:\Input\Archive

I have files in there like In20180101.txt, Intermediate20180101.txt and Out20180101.txt

The files are not being moved. It looks like it is not recognizing the filter "*" + @[User:Date] + "*.*"

If I go into windows explorer and put *20180101*.* in the Search box it does exactly what I want, shows me the 3 files containing 20180101

Can somebody tell me why this isn't working in SSIS?

Thanks,

Dick

解决方案

The Files section provides a generic

You need to specify the expression within the FileSpec

I find it easier to create a variable that does the logic rather than embed it into a container as you can inspect the value in a Variable and not in Tasks.

Solution using BIML

If you are into Biml, this will create a package that has all the work done of applying an expression to the For Each File Enumerator's File Spec based on the value of a variable DateString.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="so_48067171">
            <Variables>
                <Variable Name="Date" DataType="String">20180101</Variable>
                <Variable Name="DateString" DataType="String" EvaluateAsExpression="true">"*" + @[User::Date] + "*"</Variable>
                <Variable Name="CurrentFile" DataType="String">Demo.txt</Variable>
                <Variable Name="ArchiveFolder" DataType="String">C:\ssisdata\archive</Variable>
            </Variables>
            <Tasks>
                <ForEachFileLoop Name="FELC TodayFiles" Folder="C:\ssisdata\input" FileSpecification="*.txt" >
                    <VariableMappings>
                        <VariableMapping Name="0" VariableName="User.CurrentFile" />
                    </VariableMappings>
                    <Expressions>
                        <Expression ExternalProperty="FileSpec">@[User::DateString]</Expression>
                    </Expressions>
                        <Tasks>
                           <FileSystem Name="FST Archive file" Operation="MoveFile" >
                               <VariableInput VariableName="User.CurrentFile" />
                               <VariableOutput VariableName="User.ArchiveFolder" />
                           </FileSystem>
                        </Tasks>
                </ForEachFileLoop>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Your TODO would be to make the value of @[User::Date] based on current date or date minus one.

这篇关于每个文件枚举器中的SSIS文件筛选器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！