CODEWITHSUNDEEP
sql-serverssis
user3447031
user3447031

Reputation:

How to load multiple files in ssis

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 !

Upvotes: 0

Views: 10789

Answers (4)

Subhraz
Subhraz

Reputation: 11

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.
Image 1

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

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

The final package would look like below.
Image 4

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

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.

Upvotes: 0

vhadalgi
vhadalgi

Reputation: 7189

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 3enter image description here

In the Data flow task you might be doing this

Upvotes: 4

Tom_Doe
Tom_Doe

Reputation: 154

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.

Upvotes: 1

J.S.Orris
J.S.Orris

Reputation: 4821

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

Upvotes: 0

Related Questions