bulk loading from flat files

Question

Post reply

bulk loading from flat files

ashishchap

SSC Enthusiast

Points: 107
More actions
January 22, 2008 at 12:29 am

#76879

Hi,
I am a beginner to sql server. As a part of my first assignment, I have to load incoming flat files onto the database. The main database is in mainframe. It produces several flat files for several tables and these flat files are transferred to our server manually. My duty is to load these flat files onto the database. Our database requires only 2 or 3 fields from each of these files. The rest of the files are saved as xml.
I hope I'm being clear.
My senior told me to bulk load these files onto a staging area and load from it onto the main database. But how would I create the xml? Im only a beginner and don't know how to do any of these. Is there an example on the web on how to do this? I have been searching since the last two days and havent found any good examples that would direct me to the right way. Any help would be appreciated.
Cheers,
Ashish

Viewing 6 posts - 1 through 5 (of 5 total)

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

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

If it is a one time job you can use dts to transfer the data. There is no need of using XML.

ashishchap SSC Enthusiast Points: 107 More actions · Answer 2

Thanks for the reply.. The xml is stored in our database. We are storing the information as xml instead of the tables to minimize the storage space. XML is a MUST for our database.

sreejithsql SSCommitted Points: 1813 More actions · Answer 3

hi,

u can work on SSIS/DTS. and for XML,just rename the file with .XML.so automatically i will convert to XML.if still have doubts pls let me know.

thx

jith

MCAD

ashishchap SSC Enthusiast Points: 107 More actions · Answer 4

Thanks for the reply. The thing is for the xml, there is a specific schema. Also, we don't need to save all the fields from the flat files. We have to filter some of the fields out, load them into a xml of specified schema and save it into the database.

For eg, say the flat file has fields: lastname, firstname, address1, address2, accountid, userid

Among these, lastname, firstname and userid go into the production database and lastname, firstname, address1, address2 and userid go into the xml.

The format for the xml would be say,

(user)

(userid/)

(name)

(lastname/)

(firstname/)

(/name)

(address)

(address1)

(address2)

(/address)

(/user)

Cheers,

Ashish

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

ashishchap (1/22/2008)
We are storing the information as xml instead of the tables to minimize the storage space. XML is a MUST for our database.

Just a passing thought... maybe even a concerned observation...

As Larry the "Cable Guy" might say, "Now, that's funny right there... ya just gotta laugh at that."

To "minimize the storage space"? :hehe: Depending on the length of the element names, XML pretty much has a 16:1 bloat factor compared to other storage methods including tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)