Normalizing and loading data from csv data file

Question

Normalizing and loading data from csv data file

saidwarak01

SSCommitted

Points: 1577
More actions
June 15, 2009 at 2:05 pm

#222616

I have a csv file with data which is represented in the table as follows
Create table test1 (
Sid int
,Name varchar(20)
,Gender Char(1)
,siteid varchar(20)
,sitename varchar(20)
,readingtestdate smalldatetime
,readingscore int
,mathtestdate smalldatetime
,mathscore int)
Insert into test1 values(1,'ABC','M','M01','XX','5/08/2009',90,'',0)
Insert into test1 values(1,'ABC','M','M01','XX','',0,'06/08/2009',95)
Insert into test1 values(2,'DEF','F','VV1','VV-01','04/06/2009',88,'',0)
Insert into test1 values(2,'DEF','F','VV1','VV-01','',0,'05/06/2009',77)
Now I need to load the data into staging table along with row_number and then after loading the staging I have to use the staging table and I need to load the data into normalized tables like site table (siteid,sitename) and student table(sid,name,gender,siteid),exam table(sid,readingtestdate,readingscore,mathtestdate,mathscore).
Please help me how to load into normalized tables using SSIS.
If at all I am not clear please let me know
Thanks,
sai

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

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

fayazabdu SSC Veteran Points: 261 More actions · Answer 1

Hi

I think what you have to do is to create a staging exactly are test1 table. Extract all records from csv and dump in to staging table. Now for loading site table, Use a oledb source, write a normal sql query like "select siteid, sitename from test1" and direct it to another transform oledb destination which points against site table.. Rest will be taken care by SSIS. Hope this should help you...

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

Does it HAVE to be done in SSIS?

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

If you desire a clicky solution 😛

1.) Create a flat file source for your CSV file

2.) Add a "Sort" task to sort by SID

3.) Add a "Conditional Split" to split by "ISNULL(readingtestdate) || YEAR(readingtestdate) == 1900" and "ISNULL(mathtestdate) || YEAR(mathtestdate) == 1900"

4.) Add a "Merge Join" and add both output streams of previous split task. Chose the needed columns of both sides

5.) Destination

Done

Flo

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

saidwarak01 (6/15/2009)
Now I need to load the data into staging table along with row_number and then after loading the staging I have to use the staging table and I need to load the data into normalized tables like site table (siteid,sitename) and student table(sid,name,gender,siteid),exam table(sid,readingtestdate,readingscore,mathtestdate,mathscore).

Those tables aren't normalised. Specifically the exam table isn't. Tell me, without changing the table design, how you would store another subject's marks?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steve block SSCertifiable Points: 7698 More actions · Answer 5

Just curious, is this a homework assignment or do you work for a school?

Steve