SQLTeam.com | Weblogs | Forums

Data load question


I have excel file which is the source data with the below format.

Grade StudentId    LA     Math Science SocialStudies 
 1       1         1       NULL   1          1
 1       2       NULL      NULL   NULL       1

and would like to store the data in the below table..Please suggest the transformation to load into the desired format.


StudentID    Subject
--------     ------
1            LA
1            Science
1            Socialstudies
2            Socialstudies


ssis package, Excel connection manager, Oledb connection manager, Excel source transformation -> oledb destination transformation


Thanks for response

What transformation i have to use to transform the source columns which is in row to columsn to the table..

I have use unpivot transformation but stuck up to load the data.


pivot /unpivot should do it.