Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

foreach loop container - BIDS 2010 Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 10:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
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?
Post #1531926
Posted Thursday, January 16, 2014 11:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 12,230, Visits: 9,207
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1531933
Posted Friday, January 17, 2014 1:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
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.
Post #1532250
Posted Friday, January 17, 2014 3:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 36,015, Visits: 30,302
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532322
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse