SSIS File Filter in For Each File Enumerator

问题:

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

回答1:

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.