Post reply

SSIS variable filename - wildcard

  • June 7, 2012 at 7:17 am

    #262065

    SSIS Question

    I’m importing flat files into SQL Server but parts of the filenames will keep on changing like the date and country, file name format is below -

    10.01_20052012_UK

    10.02_21052012_US

    10.01_22052012_UK

    The only constant will be the first numbers in bold. How do I make SSIS pick the first part of the name and import that file?

    I try using the Foreach loop file enumerator in SSIS using a wildcard like below -

    10.01*.txt

    Any help greatly appreciated

  • June 8, 2012 at 3:43 am

    #1498595

    Hi Azdeji

    The Foreach Loop Controller is the way to go, please refer to the picture below and the following steps:

    0. Add the Foreach Loop Controller[/b]

    1. Add a package's variable FileName (It is up to you the name of the variable)

    2. Click on the Collection node at the Foreach Loop Editor

    3. Click on the Browse button to locate your folder; WARNING this approach hard code the folder to your package.

    The screenshot is using a package variable (@[User::targetFolder]) for a dynamic implementation.

    4. Once you navigate to locate the folder (following step 3), its name will appear on this textbox.

    5. Enter the filter criteria to select the files you want to process.

    6. Make sure that the Fully qualify option is selected; it ensure the fully qualified name is assigned to the variable @[FileName]

    7. Click on the Variable Mapping node.

    8. Select the variable that will contain the names of the files returned by the Foreach Loop

    The the variable @[FileName] contains the file name and it will be available for any task or component within the Foreach Loop area.

    Cheers,

    Hope this helps,
    Rock from VbCity

  • June 8, 2012 at 9:30 am

    #1498799

    Thank You For the Comprehensive Reply!!!

    Can you help me with one more question releating to folders and Variables?

    Is there any way to retrieve the folder name for example ‘ABC’ as part of a variable? c:\ABC\test.csv

  • June 8, 2012 at 1:08 pm

    #1498896

    What a nice thorough answer.

  • June 8, 2012 at 1:56 pm

    #1498929

    azdeji (6/8/2012)

    Thank You For the Comprehensive Reply!!!

    Can you help me with one more question releating to folders and Variables?

    Is there any way to retrieve the folder name for example ‘ABC’ as part of a variable? c:\ABC\test.csv

    Fully qualified, as Rock indicated you should use, will get you your folder path AND filename in the variable it's delivered to.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply