How to traverse multiple folders & multiple files

问题:

This will be a bit of an update from the question I asked here before.

I need to traverse folders & dump txt files to SQL tables with the same name (barring the .txt extension)

My folder/file structure is setup as shown below

Now the FileA,B,C are consistent throughout all the folders & there happens to be a [dbo].[FileA],[dbo].[FileB],[dbo].[FileC] etc sitting on the server.

So Data1 Folder will have FileA,FileB...FileZ & so will Data200 Folder.

The goal is to traverse through all the folders & take the files & dump them into the respective sql tables.

There are no discrepancies when it comes to number of columns etc on the tables. The first row doesn't hold the Column Header. I'm not sure how one can parse that aspect out from a ssis package.

A previous attempt resulted in a VS_NEEDSNEWMETADATA error which I've not been able to sort out.

If not the server part , is there a way I can copy/move all FileA (s) to one folder, all FileB (s) to another etc after traversing through all the folders

回答1:

You will have to have a ForEach loop file enumerator for each file type:

1. wrap your Data Flow A task in a foreach loop and set to file enumerate
2. set the folder to your parent source location
3. check traverse subfolders
4. set search string to FileA*.*
5. set to full file path
6. map to a variable called fnameA
7. use fnameA in expression on ConnectionManagerA
8. Make sure you delay validation on connection manager

This pattern will take care of all File A.

You will need to repeat for FileB to FileZ as they will have different data flows.

Good Luck!

回答2:

You can achieve it with the same steps from Import multiple flat files to multiple SQL Tables but you need to change *.txt to FileA.txt in the foreach loop container (step no. 4). and repeat this for each file type. FileB.txt , FileC.txt , ...