FLat file Normalization

Question

FLat file Normalization

Ali Zaman

Old Hand

Points: 338
More actions
July 19, 2007 at 3:33 am

#196981

Hi Can Any one help,
Well i got a flat file (in{CR}{LF} format) and needs to be imported into a sql server 2000.
now i need to know how can i create a package using DTS or some thing which kindoff breaks the data down and insert it into appropriate tables from the flat file. I need to find a way to carry it out in one process as the data is imported through the pipe. SO i have multiple tables which would eventaully contain the data from flat file.So ill Be getting the Data after every two weeks which will need to be imported into SQL server 2000. So is there any way i can automate the process.
regards

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

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

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

What I have done is to create a job with a series of stored procedures. I use a sproc to Bulk Insert the contents of the file into a "working" table. Then have a series of procedures that check and modify the data (if necessary), load values into foreign key tables, and then load each of the data table.

Normally, each step would be a separate sproc, just to break down the work and make it easy to modify the order (if necessary). Each job step calls one sproc, so that you can easily use the step completion to stop the job or bypass a step or two if their is a problem. If I need to have updates to two or more tables to be synced, then I will put the updates to those two tables in one sproc so that they can be rolled back if needed.

Finally, if this will be unattended and automated, then I make sure to put out email notices from the jobs with the results.

Hope this helps.

Mark

Ali Zaman Old Hand Points: 338 More actions · Answer 2

Hi Mark,

Thanks for your response. at the momont as you suggersted i am trying a mix of cursors and lot of store procedure to retreive data and check it in relevent tables.

not too sure how you do it by creating jobs and dividing it in different steps.

So i am litrally executing each store procedure from query analyser which contains cursors and are hooked up with relevent store procedures. I know not the ideall way to go around it, unless you could be bit more specified about doing it with jobs.

regards

Jim C-203340 SSCommitted Points: 1716 More actions · Answer 3

Ali,

I've handled this using DTS by creating a Working table like Mark suggested and setting up my flat file as a txt or xls file source. I run through scrubbing and checking procedures using the DTS "Execute SQL task". I then use a series of transformations to migrate the data to the required tables. I set up precedence on the transformations to be sure the Primary tables are loaded before the referencing tables.

Regards, Jim C

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Ali,

What is the record format for the flat-file you are trying to import?

--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)

Ali Zaman Old Hand Points: 338 More actions · Answer 5

Ali Zaman

Old Hand

Points: 338

July 20, 2007 at 4:42 am

#721046

Fixed lenght rows.......

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Yep, kinda figured that... was hoping you would post the actual record layouts...

--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)