Post reply

foreach loop container - BIDS 2010

  • January 16, 2014 at 10:37 pm

    #281258

    Starting with the most basic Data Flow Task I want to learn how to use a Foreach Loop Container to move the data from two text files into a sql table. But the moment I put the DTF into the Foreach, it is failing to pick up the files needed to process the data.

    The DataFlowTask I currently have (and works) just reads File7.txt from the directory C:\EMP and loads its' contents into a table called EMP.

    DataFlowTask

    Flat File Connection->OLEDB Destination

    Next, I've put a second txt file, File8.txt, in the source folder and now I want SSIS to perform the DFT for each of them. So, I put the Dataflow task into a Foreach Loop Container, edit the container to use a variable in place of filename, and then I modify the Flat File Connection to use the variable defined in ForEachLoop with which to to connect to source file (instead hardcoded filepath C:\EMP\File7.txt.

    Foreach Loop Container Edits

    Collection

    Folder: C:\EMP

    Files: File*.txt

    Retrieve File Name: Name and Extension

    Variable Mappings

    Variable: User::filename

    Index: 0

    Flat File Connection Manager modifications

    Clicked properties and added @[User::filename] to Expressions and selected "ConnectionString".

    When I run this it errors on the Flat File Connection and execution results say:

    [Flat File Source [2]] Information: The processing of file "File7.txt" has started.

    [Flat File Source [2]] Warning: The system cannot find the file specified.

    [Flat File Source [2]] Error: Cannot open the datafile "File7.txt".

    [SSIS.Pipeline] Error: Flat File Source failed the pre-execute phase and returned error code 0xC020200E.

    File7.txt is in the C:\EMP folder and is mapped to a variable.

    Where is the problem?

    --Quote me

  • January 16, 2014 at 11:54 pm

    #1680966

    You are retrieving only filename and extension, so the folder is missing.

    In the for each loop you can choose to retrieve the fully qualified path. Take that one.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 17, 2014 at 1:06 pm

    #1681229

    Fully Qualified eventually worked. I had to rebuild the package....so I must have had something else wrong as well.

    Thanks for standing by. At least I knew it had to be a very limited number of things and I wasn't way off. Thanks.

    --Quote me

  • January 17, 2014 at 3:59 pm

    #1681296

    This appears to be a duplicate thread, which splits up the answers. Please, no more replies on this thread. Please post your answers at the following thread.

    http://www.sqlservercentral.com/Forums/Topic1530927-148-1.aspx

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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