getting at the guts of multiple flat files

Question

Post reply

getting at the guts of multiple flat files

dgates-756279

Old Hand

Points: 334
More actions
June 9, 2008 at 5:13 pm

#208181

Hello all,
So I have some flat files which I need to access, parse, store specific info in a database, and then move - the good news is that they all end in .dat and are in the same directory 😀
So, what I have set up right now is:
Control Flow
~ Foreach Loop Container
- Collection
- Folder:
- Files: *.dat
- Variable Mappings
- User::varFileName (Index = 0)
Data Flow
~ Flat File Source
- Flat File Connection Manager: VariableChargesFileDAT
Note that ~ denotes a component and - denotes a setting in a component
I am stuck when it comes to referencing varFileName as the File Name in the Flat File Connection Manager.
Any help is most appreciated!
Dave.

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

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

haroon waheed SSC Veteran Points: 261 More actions · Answer 1

Please follow these steps:

-Right click on Flat file connection manager and select properties

-From the property window expand Expressions

-Select connectionstring in property list box and expand expression

-In the expression editor drag the variable that contains the file's complete path in the text area below

-Click OK

This will change the connection string property of the flat file to a SSIS variable, in your case it will be the complete path of the text file that you evaluate in the foreach loop.

Hope it helps!

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 2

What caught my eye was that you were optimistic that the files end in "*.dat"

Hope the layout is the same of the files, if not, you will need a connection manager per layout, even if it is running in a for each loop container

~PD

dgates-756279 Old Hand Points: 334 More actions · Answer 3

all the files have the same structure, so that is good too! 🙂

The issue I was having was that when I was only using the variable and expressions I was unable to set up the columns - so what I did was select a file using the 'normal' (non expression) way in the general tab. When I ran it the 'normal' one disappeared. So to make adjustments to columns I just need to browse to the file using 'File Name' in the general tab.

Thanks again for the help!

Dave