pass filename to flatfilesource inside foreach loop

Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.

The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?

Please note, this is a different question to my other post.

Many Thanks

Hi

create a flat file connection and in expression give connectionstring as the variabe you have created in for each loop

for give this flat file connection as flat file source

|||

Hi,

I've done the first part already.

Not sure what you mean by:

"for give this flat file connection as flat file source"

|||

Are all of your files going to be the same record structure / column layout? If so then all you should have to do is add the filename variable to your connectionstring property of the flat file connection manager. (I would also put the full path into this expression).

If you have done this already, is it working? If not, what errors are you getting?

|||

Hi,

The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?

|||

You would create the filename variable at the package level and use it in both the flat file connection and the foreach loop.

It will set the variable in the loop and use the variable in the connection manager.

In other words, in the foreach loop, make sure that the foreach file enumerator is selected, choose the file name convention you would like to return (preferably fully qualified if you are using this with a flat file connection), go to the variables tab and choose your variable name (User::FileName which should be declared at the package level). In your flat file connection go to your expressions and choose connectionstring as the property to edit. Select the User::FileName as the variable.

That should do it.