effort to learn Foreach Loop Container - BIDS 2010

Question

effort to learn Foreach Loop Container - BIDS 2010

polkadot

SSChampion

Points: 13664
More actions
January 14, 2014 at 6:19 pm

#287222

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 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

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

polkadot (1/14/2014)
Variable Mappings
Variable: User::filename
Index: 0
Flat File Connection Manager modifications
Clicked properties and added @[User::filename] to ConnectionString.
File7.txt is in the C:\EMP folder and is mapped to a variable.
Where is the problem?

You need to use the "Expressions" property for the connection manager for this. If you look at the properties of the connection manager, not only is there a "ConnectionString" property but this is also an "Expressions" property. The expressions property is a collection that allows you to assign a value to properties at runtime - one of which is the ConnectionString.

polkadot SSChampion Points: 13664 More actions · Answer 2

Actually, that's what I meant. I have nothing in the CollectionString property. I should not have said that I added the variable to the ConnectionString property, rather I put the variable into the Expressions field as a 'ConnectionString'.

Anything else I can do?

--Quote me

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

polkadot (1/14/2014)
Actually, that's what I meant. I have nothing in the CollectionString property. I should not have said that I added the variable to the ConnectionString property, rather I put the variable into the Expressions field as a 'ConnectionString'.
Anything else I can do?

Have you tried setting

Retrieve File Name as: 'Fully Qualified', rather than just 'Name and Extension'?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

polkadot SSChampion Points: 13664 More actions · Answer 4

Thanks Phil. Yes I have and neither way has it worked. I understand the Fully Qualified pulls the path + filename into the variable, where as just Name and Extension pull just the filename.txt into the variable. I would think both ways should work.

--Quote me

polkadot SSChampion Points: 13664 More actions · Answer 5

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