CODEWITHSUNDEEP
sql-serverssisoledbetlflat-file
thestralFeather7
thestralFeather7

Reputation: 519

How to traverse multiple folders & multiple files & dump data to SQL tables with same filename? SSIS

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

Folder setup

enter image description here

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

Upvotes: 3

Views: 1699

Answers (2)

Hadi
Hadi

Reputation: 37313

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 , ...

Upvotes: 0

KeithL
KeithL

Reputation: 5594

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!

Upvotes: 1

Related Questions