How to load multiple files in ssis

Question

i am very new to ssis !

i wanted to know the below.

How to load multiple files into sql-server using ssis tasks?

I am currently working on datawarehouse. i have a bunch of files each in separate folders

the folders are

Myfolder
  ->subfolder_21-03-2009.txt
  ->Mysubfolder_21-03-2009.txt
Myfolder1
  ->subfolder_22-03-2009.txt
  ->Mysubfolder_22-03-2009.txt
Myfolder2
  ->subfolder_23-03-2009.txt
  ->Mysubfolder_23-03-2009.txt

I am able to load Subfolder.*.txt into sql-server DB(table 1) using foreachloop In ssis..

now can somebody tell me how'd i load mysubfolder.*.txt into Sql-server(table 2) DB

i want to load multiple files into sql-server in a single process.

please somebody guide me !

Package is jus a for each loop with. Dft to load flat file into db with a String variable to iterate, but need to know how could I make a package that could I load 2 or more different files into 2 or more tables respecyively — , Mar 30 '14 at 18:20
i have i similar question in mind so i started a biunty on this :) — vhadalgi, Mar 31 '14 at 13:51
Duplicate of [Import text files](http://stackoverflow.com/questions/19957451/how-to-import-text-files-with-the-same-name-and-schema-but-different-directories/19957728#19957728) but cannot be closed yet due to the bounty — billinkc, Mar 31 '14 at 14:58
@billinkc : i dont want to use the BIDS helper... i wnat it in SSIS only thnaks ! — , Apr 02 '14 at 08:32

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

I am currently working on datawarehouse. i have a bunch of files each in separate folders

seeing this you need to refer this answer also you may check this

and this

this is quite helpful you can take a look here

To answer above you got a sequence container that executes task one after other :

Snapshot 1

Snap shot1

Snapshot 2

You might want to give Mysubfolder.*.txt

Snap shot2

Snapshot 3

In the Data flow task you might be doing this

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 02 '14 at 16:12

vhadalgi

7,027
6
38
67

thanks man ! i did the same way..is there any alternative to above,just for knowledege :) – vhadalgi Apr 03 '14 at 12:57

score 1 · Answer 2 · answered Mar 31 '14 at 20:57

You allready have a "ForEachLoopContainer" looping in each folder to load "subfolder_*.txt" in table 1.

Why don't you create a second "ForEachLoopContainer" looping in each folder to load "Mysubfolder_*.txt" in table 2?

Then put those 2 "ForEachLoopContainer" in a sequence container and it's done.

score 0 · Answer 3 · answered Mar 28 '14 at 22:48

If it is a directory of files. Use a ForEach Loop container to enumerate all your files in the directory; because you have subfolders you must click the "traverse subfolders" option. Next you need to put a Data Flow Task in your ForEach Loop Container. Within the Data Flow Task you need to add a Flat File Source. Then you need to add a Derived Column transformation after your Flat File Source. Within your Derived Column you need to add the variable that you used to loop through the directory into the "Derived Column Name"; in the "Derived Column" put "Add as new column", and in the "Expression" you would use a FindString expression or a combination of LEFT/RIGHT expressions to grab the date from the file name. Then in your OLE DB Destination object you need to map your derived column to the relative column in your SQL-Server DB.

FINDSTRING: http://technet.microsoft.com/en-us/library/ms141748.aspx

LEFT/RIGHT: http://technet.microsoft.com/en-us/library/hh231081.aspx

Hmm..this is not what I need man I upvoted your other answer on my question! But this doesn't help me, can you please look above question again ... Thanks ! — , Mar 30 '14 at 18:17
Did you check the "traverse subfolders" option in your ForEach Loop container? — J.S. Orris, Apr 03 '14 at 18:42

score 0 · Answer 4 · edited Jun 26 '18 at 23:05

I wanted to check to load 3 files with different format into 3 separate tables. Let’s say I have 3 files A, B, C with date as suffix and that changes every day. This is what I did.

Create a ForEach Loop Container and set the Collection tab as below.

Let’s make the filename date part dynamic using expression for each file connection managers.

The final package would look like below.

And voila!! The tables are loaded from respective files.

Obviously this can be refined by using Foreach File Enumerator getting the fully qualified name of the file in a variable and then writing an Expression to parse it to channelize to different DFTs. Hope this helps.

How to load multiple files in ssis

4 Answers4

Snapshot 1

Snapshot 2

Snapshot 3