ForEach from query

Hi All

I'm sure this is a simple thing to do, but I'm new to SSIS and trying to catch up fast.

I want to execute a query on the database which will give me a path and a filespec, say:

c:\apps\testapp1

and

fred*.csv

No problems here.

I then want to feed them into a ForEach loop and interate through all the files matching the filespec at that location. I can't figure this out at all.

Thanks for you help in advance.

FG

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Jamie's blog has good info on how to use the ForEach loop container with the ForEachFile enumerator. You can supply a filepath and extenstion with wildcard, fetch back filenames and update a connection strimg. Good stuff.

But I think what you were asking was how to dynamically update the enumerator with different paths and filenames? First it might depend on what your really trying to do.

If you just want to stream in the files then you can also not bother with the loop and instead update the connection string of a 'multiflatfile' connection manager with a property expression of what the file info should be. The key diffference between the multiflat file is that it will accept wildcards...so it can take c:\test\*.txt and the flat file source that uses that conection manager will just load all of the files. So, its functionally different than Jamies route...both have their uses. Looping over the files will load them one at a time, starting/stoping the dataflow each time BUT you can get file specific information such as useing rowcount transform. If you used a rowcount with the wildcard approach and multifileconnection mgr then you just get 1 rowcount result which would include all rows from all files. Again, each method has its place.

Now I think what you really are asking is how to tweak on the fly the folder (directory) and files (fielspec). Well, unfortunately you cannot use property expressions on those properties. Its a current limitation. They are not really properties of the ForEach Container but of the specific enumerator (ForEachFile) which you chose. However you can do it indirectly, using Configurations and having 2 packages, one calling the other, passing in the appropriate new values.

So the parent package uses and ExecuteSQL task to fetch the inforation from a table,returning "path" and "extension" to 2 varirables, all defined in the ExecuteSQL task.

You create a 2nd package with a For Each Loop.
Child: you create 'package Configurations' of the type "parent Package Variable'. one maps to the 'filespec' property and one to the 'directory' of the For each loop

Parent: Then from the parent you add an ExecutePackage task which calls the child package.

So flow is...

Parent ExecuteSQL to populate 2 vars
Parent Executes Child Package
Child Configurations are first thing to be 'pulled' from parent as Child package starts
Child ForEach Loop excutes and the appropriate properties are already update

I suggest reading aobut parent package configurations if you have not already. I think I also have a sample I could send you.

Hope that helps

|||

Very many thanks for both replies.

Craig is correct in his understanding of what I am trying to do. ie, get a path and a filespec from the DB and use these to control the ForEach loop. I'm pleased to hear that it can't be done directly at present, I hadn't missed something too obvious!

I will try your suggestion shortly Craig.

Thanks to Jamie for his input too.

FG