Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: SSIS: Flat File Source with Wildcarded Filename

    My task is to write an SSIS package that picks up just one file from a directory and loads it into a database table. The filename is defined as being "ABC*.txt". So I must pick up only one file that matches that wildcard.

    I can see two ways of doing this, but I can't get either to work:-
    1. Use a Flat File Source connection and put the wildcard in the ConnectionString.
    i.e. ConnectionString = "C:\\mydir\ABC*.txt"
    But SSIS doesn't seem to support that.
    2. Use a Foreach Loop Container with a Foreach File Enumerator, and configure the enumerator as:-
    Folder = c:\mydir
    Files = ABC*.txt
    This works well, but loops round for as many files match the wildcard. Is there any way of forcing it to drop out after the first time round the loop?

    Or am I missing a much easier solution?
    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Would
    Code:
    ConnectionString LIKE "C:\\mydir\ABC%.txt"
    this work?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Hi Georgev. Unless you know something I don't ConnectionString is a box you fill in. So you can only assign a value, you can't use 'LIKE' or any other operator. It's actually stored in the package as XML as follows:-
    <DTS:PropertyExpression DTS:Name="ConnectionString">"C:\\mydir\\ABC%.txt"</DTS:PropertyExpression>
    But I tried it anyway, putting "C:\\mydir\\ABC%.txt" in the box, and got the following message:-
    Error: 0xC020200E at Data Flow Task, Flat File Source [1]: Cannot open the datafile "C:\mydir\ABC%.txt".

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It was a long shot, eh?
    Well the problem I see is that, as you mentioned, you cannot use the LIKE operator, so really you have to specify the *exact* value.

    I think that if the file is not fixed then you are going to struggle with this!
    I'd really like someone to prove me wrong though!!
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does it have to be SSIS?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    haven't had to build a 2K5 SSIS (yet) but conceptually this might work. Have a task create a table to hold results of a "dir" command executed by xp_cmdshell. Select top 1 row from the table based on your criteria, and use that filename to execute yoiur Flat File Source Connection?

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by tomh53
    haven't had to build a 2K5 SSIS (yet) but conceptually this might work. Have a task create a table to hold results of a "dir" command executed by xp_cmdshell. Select top 1 row from the table based on your criteria, and use that filename to execute yoiur Flat File Source Connection?

    You mean like this

    http://weblogs.sqlteam.com/brettk/ar...6/28/6895.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Quote Originally Posted by tomh53
    haven't had to build a 2K5 SSIS (yet) but conceptually this might work. Have a task create a table to hold results of a "dir" command executed by xp_cmdshell. Select top 1 row from the table based on your criteria, and use that filename to execute yoiur Flat File Source Connection?
    It's a bit convoluted but you're right, this will probably work.
    Thanks,
    - Andy Abel

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's convulted is using SSIS to do anytrhing (or DTS in 2k)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    OK, I have an even better solution... use a loop count in the Foreach block...

    I added an SSIS variable called file_count, and set it to 0.
    Then, inside the Foreach Input File I added an "Execute SQL Task"
    and set SQL Statement to: select (? + 1) as file_count
    In the Parameter Map set the input variable to User::file_count
    Set it to a "Single row" Result Set and assigned file_count to User::file_count
    (This provides the loop counter).

    Then, from the "Execute SQL Task" I added an Expression Constraint (@file_count == 1) to the first task. So the rest of the tasks will only execute for the first pass through.

    I've tested it and it works a treat!

  11. #11
    Join Date
    Oct 2002
    Location
    Warrington, Cheshire, UK
    Posts
    1

    Talking Wildcard Syntax for "Files" box on SSIS File Enumerator

    It works like the MSDOS dir command so to list only csv's beginning with AAE, you'd write:

    AAE*.csv

    If you have a space in your prefixes, you need remove them by doing a mass-rename in Windows Explorer. Select all files, right-click the selection and select Rename. Carefully rename one file that is shown as a usual rename and press Enter. It will apply the change (removed space) to all selected files. V useful.

    You can test all this out by running "cmd" in your Run box on Windows Xp - change to the folder using "cd".

    Foreach Loop Editor (Collection Page)

    Peter Brennen, NHS England
    Last edited by pab98c; 08-17-11 at 14:17. Reason: Fix

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •