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: 29387 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: 246695 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'?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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