Importing all text files at once into DB

gvamsimba
gvamsimba used Ask the Experts™
on
HI,I have a folder which contains about 25 text files with similar column
names in all the files.. I want to import them all into my sql server as
25 tables..i am currently doing the process manually using import
and export wizard.. Is there any way to automate this process by using
SSIS so that i need to run it only once ? Is so can you please explain
the process step by step ?


Many Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Take a look at the post I wrote here:
http://biresort.net/blogs/pedrocgd/archive/2009/05/14/stepbystep-ssis-moving-huge-amount-of-files.aspx

Download the attached document in the link and read!

Helped?
Regards,
Pedro
gvamsimbaIT Consultant

Author

Commented:
Many Thanks for the information Pedro.. But my destination is the sql server DB and not another folder..in this case, how do i set the dynamic variable
for this ?

Commented:
all teh 25 files has the same schema!?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

gvamsimbaIT Consultant

Author

Commented:
yes all the 25 files have same columns ...

Commented:
I do a package example for you. could you attach here a sample?
regards,
Pedro
gvamsimbaIT Consultant

Author

Commented:
Pedro, it's exactly the same requirement like ur sample which i have downloaded...only difference is i m not moving the files to another folder or location ? i just need to load all those 25 files into 25 different tables in a single database in sql server...also the files in the source folder should not be deleted from the folder after import is finished....they need to just sit there...

so can u just make this small change in ur sample package itself and attach the solution ?

Many thanks
Vamsi.

Commented:
I can do an example for you... just tell me one more thing:
The name of destination table is based on the source textfile?!
Regards,
Pedro
gvamsimbaIT Consultant

Author

Commented:
Yes  please..the name of the destination table is based on source text files..
many thanks..

Commented:
Here you have...
Update cmSQL connection and the variable ubTextPath to the folder where you have your textfiles
Execute SQL Script to create destination tables (based on source filenames)
Create a new SSIS Project and add the attached package.

Helped?
Regards,
Pedro
www.pedrocgd.blogspot.com
www.BIResort.net
SSIS-Interface.JPG
CreateTablesSQL-sql.txt
source1.txt
source2.txt
Package-EE117-dtsx.txt
gvamsimbaIT Consultant

Author

Commented:
HI Pedro, but the package is in xml..how do i add it to the existing packages ?
sorry, i m bit new to ssis..

Commented:
XML?!
rename to dtsx! :-)
FROM Package-EE117-dtsx.txt
TO Package-EE117-dtsx

And add it to a new SSIS Project
gvamsimbaIT Consultant

Author

Commented:
Thank you very much pedro.. but since i have 25 text files, do i still need  to create my destination tables which will be 25.. is it possible for you to  modify the package so that i dont need to create 25 destination tables  by writing code and the package will do it automatically ?

so from my understanding we just need to mention the name of the first file in the variable 'uvFileName' and then all the text files will be imported automaticlly.. am i right ? what if i want to import only the most recently modified
files or those modified during the last two days  or so ? can you include a script task for this in this package ?

Many Thanks
Vamsi.
Commented:
HI vamsi,
You cannot create tables in destination database in runtime from SSIS... you can create autimatically in BIDS during development of the package.

"so from my understanding we just need to mention the name of the first file in the variable 'uvFileName' and then all the text files will be imported automaticlly.. " YES

For import new files follow the attached document that is placed in.
http://biresort.net/blogs/pedrocgd/archive/2009/05/14/stepbystep-ssis-moving-huge-amount-of-files.aspx
Just add the script task to the current example I made for you.

Helped?
Pedro
www.pedrocgd.blogspot.com


SSIS-EX-02-FELC.pdf
gvamsimbaIT Consultant

Author

Commented:
oh ok, but the import and export wizard creates the destination tables on the fly..so i thought SSIS can do it as well .. so which means i need to create
25 destination tables manually right ?

 the scrip task in ur old package is completely moving the files out of the source folder..but i want them to stay where they are after the imports are finished.. can u please tell me how and where to make that change ?

Many Many Thanks Pedro.. I appreciate your help and patience..

Commented:
Here yoiu have a new version of the package! I hope it helps you!
Also attached an example of creating a table in destination in the fly... not in running, but in developing
Pedro
www.pedrocgd.blogspot.com
Package-EE117b-dtsx.txt
SSIS-createtable.JPG
gvamsimbaIT Consultant

Author

Commented:
thanks pedro,but the data from both the files is being imported into
this destination table, which i dont want.. i want each of my text files from
my source to get imported into each different 25 destination tables..
here is the output i m getting into my destination table..

1      50      some data
1      100      some data ....
3      23      some data 2 source
6      450      some data 2 source....

Commented:
Sorry, but I have the data split into corrected destiantion tables... like you can see in attached image...
Maybe you made some transformation...
Regards,
pedro
DestinationData.JPG

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial