SSIS Package to import data from txt files to Tables

Question

SSIS Package to import data from txt files to Tables

kishore.kk

Old Hand

Points: 362
More actions
May 13, 2008 at 1:21 am

#185402

This question is regarding SSIS:
I am trying to loop through a directory of directories that contain directories which contain files. i.e:
C:\databases\client1\client1.txt need to map into Table Client1
C:\databases\client2\client2.txt need to map into Table Client2
C:\databases\client3\client3.txt need to map into Table Client1
Does anyone know how to do this? I was thinking a foreach loop, but I don't know how to create connections and looping.
Thanks in Advance,
kishore

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ulrich Bauhofer Say Hey Kid Points: 700 More actions · Answer 1

Hello,

you can set the different filenames (including the path) in the properties of a connection (Connection Managertype = FILE). Just create a Connection to on of your files. The name of the property is 'ConnectionString'. The property can't be set with a wizard.

Before create a UserVariable (i.e. fileName)

Click on your connection and select properties. In the property-window select Expressions. There you can choose the property ConnectionString. Click on the ellipsis. In the Window you can choose your variable an drag it in the window beneath (Expressions).

One possibility to fill the variable fileName is the tool FileWatcher (www.sqlis.com). Let it run in a loop.

kishore.kk Old Hand Points: 362 More actions · Answer 2

Thanks for yor reply. But How can I loop for Table names. How I will give connection string for Tablenames. Each time it should take different table name.

Thanks & regards,

Kishore

Bindu-330959 Old Hand Points: 362 More actions · Answer 3

Hi

I had faced the same problem. There is a limitation with SSIS. You can not use For each loop inside another for each loop to loop through some configuration table to get the mapping information.

Here is the work around comes to my mind:

You can have a for each loop to loop through the files in the given path. This for each loop should output the filename into global variables.

Based on the file name you can have data flow task to push data from txt file to the table. This data flow task should have precedence constraint which will check the file name(global variable) and based on this the data flow task will execute.

Ex: if the file name is file1.txt then data flow task which pushes data to table1 should get executed.

Here you need to create different Flat file connection managers if the file format is different for each file.

Hope this helps you.

Regards

Bindu

kishore.kk Old Hand Points: 362 More actions · Answer 4

Hi Bindu,

Thansk for your repply.

And if you dont mind, can I ask you some help. As it is very urgent task, need to migrate data fron Flat files to DB. So I have to finish it with in a week. Can you please provide some more information.

If u don't mind please give your mail id, i will give the details.

My Mail id: Kishore.kk@tcs.com

Regards,

Kishore

Ulrich Bauhofer Say Hey Kid Points: 700 More actions · Answer 5

Hi,

create a Table containing the filennames (or a part of them) and the tablenames. When you get the filename, you can search the tablename in this table (ExecuteSQLTask). Let the output be written in an uservariable.

Read the file in a temporary used table. With the uservariable filled above and one a StoredProcedure (parameter tablename) you can insert the content of your temporary table in the designated table. Afterwards truncate the temporary table

sai_kumar554 Valued Member Points: 62 More actions · Answer 6

hi,

Please send me solution for importing two or more text into table at one time into sql server 2005 DB.

Please send its urgent.

Sai.

saikumar554@gmail.com

sai_kumar554@yahoo.com

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

Assuming that the text files are the same format (same field names, types, number of fields) you can use a FOREACH loop to iterate round the files.

But I'm not going to do it for you and send it to you - you need to learn this stuff yourself, or pay someone else to do it for you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

perdijc SSC Journeyman Points: 98 More actions · Answer 8

I would like to pay someonw to give me an example reporting import data from diferent flat file to s to different tables.

I mean, is need to build a package, running a loop by files and importing the data.

Someone can help me?

perdijc@gmail.com

Regards

kishore.kk Old Hand Points: 362 More actions · Answer 9

Send me the details of req. to Kishore.kk@tcs.com