Home » SQL ServerRSS

SSIS data extraction question?

I am trying to load data from a flat file. Here is some sample data:

100 John Science 75
100 John Biology 70
100 John Math     80

101 Dave Science 75
101 Dave Biology 70
101 Dave Math     80

I need to put this into a table called 'students' whose structure is: studentid, studentname, science, biology, math

is there anything in SSIS that I can leverage and get this done in an easier and efficient way - any input or help would be appreciated?

thanks.

 

5 Answers Found

 

Answer 1

Hello test_user_,

The key is in the proper mapping your data fields in the file to the table columns.

A not a bad walk-through is here: http://decipherinfosys.wordpress.com/2008/07/28/ssis-importing-data-from-a-text-file-using-a-package/

Besides, I assumed you load a tab delimited file to a SQL Server.

Not a bad idea to also get some tips on this technique, so here are some from BI Monkey: http://www.bimonkey.com/2009/06/flat-file-import-tips/

He also has a good set of other SSIS related articles (may be helpful).


Arthur My Blog
By: TwitterButtons.com
 

Answer 2

Hello, you can achieve this by two ways

1--Extract the data from flat file and then Pivot Transformation in ssis and finally load into your destination table. To configure Pivot Transformation is little confusing sometime when you are doing first time.

here is link , you can follow step by step to configure it.

http://www.youtube.com/watch?v=ZoCxAksokJM

2--Create your stating table 

CREATE TABLE dbo.MyStatingTable( Studentid int, StudentName varchar(30),Subject VARCHAR(30), Grades INT)

Load the data from flat file into this staging table, See the columns are exactly matching with flat file columns, just dumping data into staging table.

After dumping data , Write this TSQL Query and use this in Execute SQL task to load the destination table 

INSERTINTO dbo.MyDestinationTable
      (StudentId,
       StudentName,
       Science,
       Biology,
       Math)
SELECT *
FROM  dbo.MyStatingTable PIVOT ( MIN(Grades) FOR Subject IN ([Science], [Biology], [Math]) ) AS PV 

Thanks

 

Answer 3

Aamir is correct that in the example you gave us you want to use the Pivot transformation. However, are you sure you want to have 3 distinct columns for Science, Biology, and Math and not make that a 'Subject' or 'Course' column? Then you would have the final score in a 'Grade' column. Which brings me to point out that with the sample data provided you are not  placing the final column numbers anywhere...
Brian
 

Answer 4

I am trying to load data from a flat file. Here is some sample data:

100 John Science 75
100 John Biology 70
100 John Math     80

101 Dave Science 75
101 Dave Biology 70
101 Dave Math     80

I need to put this into a table called 'students' whose structure is: studentid, studentname, science, biology, math

is there anything in SSIS that I can leverage and get this done in an easier and efficient way - any input or help would be appreciated?

thanks.


Two words. Pivot Transformation.

 

 

[Everyone else said the same but then said other stuff and I wanted to be succinct :) ]

 

Answer 5

Aamir - Thank you so much for your reply and the video. That solved my problem. Appreciate your help.

 

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter