CODEWITHSUNDEEP
ssis
Dataman
Dataman

Reputation: 27

Wildcard search in SSIS foreach loop container

How can I perform a wildcard search if the varying string is in between

Example:

I know Tree_Mango_* works if the required files are Tree_Mango_1,Tree_Mango_2, etc..

But if what I need is Tree_1_Mango, Tree_2_Mango, Tree_3_Mango, etc... then how would I specify the wildcard?

Upvotes: 1

Views: 4749

Answers (1)

billinkc
billinkc

Reputation: 61249

The wildcard for the Foreach File enumerator container is going to perform the same as the Windows/DOS OS.

* will, optionally, match anything

? will match one character

Given the source values of

  • Tree_1_Mango
  • Tree_2_Mango
  • Tree_3_Mango

I would use a FileSpecification of Tree_?_Mango

Given the following contents of my directory

C:\SSISData\so\32061364>dir /b /s
C:\SSISData\so\32061364\Tree_1_Mango
C:\SSISData\so\32061364\Tree_2_Mango
C:\SSISData\so\32061364\Tree_3_Mango
C:\SSISData\so\32061364\Tree_4_MangoDB
C:\SSISData\so\32061364\Tree__Mango

And I used Biml to create a simple package for a foreach file enumerator. Unfamiliar with Biml? Install the free add-on BIDS Helper. Add a new Biml file and paste the following declaration. Right click and Generate SSIS package. 

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="TreeMango">
            <Variables>
                <Variable DataType="String" Name="CurrentFileName">mixed</Variable>
            </Variables>
            <Tasks>
                <ForEachFileLoop 
                    Folder="C:\SSISData\so\32061364" 
                    FileSpecification="Tree_?_Mango" 
                    Name="FELC Tree Mango">
                    <VariableMappings>
                        <VariableMapping VariableName="User.CurrentFileName" Name="0" />
                    </VariableMappings>
                </ForEachFileLoop>
            </Tasks>
        </Package>
    </Packages>
</Biml>

The result is that Tree_1_Mango, Tree_2_Mango, Tree_3_Mango match my wildcard. Tree_4_MangoDB and Tree__Mango were not matched.

Upvotes: 3

Related Questions