loading data from csv

Question

hi,

i have two csv file team.csv and member.csv  as shown below

team.csv                member.csv

tid, tname               tid, mid, mnane
1,  team1                1,   100,   m1  
2,  team2                1,   200,  m2  
3,  team3                2,   300,  m1 

i want to create package which load data from these csv  file into following table at same time

team table          member table           team_member

tid tname            mid  mname            tid  mid  tname  mname 
1   team1            100  m1                   1     1     100      m1
2   team2            200  m2                   1     1     200      m2
3   team3            300  m3                   2     2     300      m1  

how can achieve this functionality

plz help

              

Answer 1

Thats straight forward.

Use 2 flat file source for each file.

Put a multicast after both of them.

from each multicast pull one output and send to oledb destination.

From the multicasts select another output and sort the data by tid.

Next with these sorted sets use MErge join and the output send it to OLEDB Destination.

---

My Blog    |      Ask Me     |      SSIS Basics     

Answer 2

Hi,

Load your two tables and then insert data into team table then make script task to separate data from member.csv to the two tables and then make to output one for member table and the other for team member.

I hope this is helpful.

Elmozamil Elamir

MyBlog

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.com

Answer 3

Baba,

In the OLEDB Destination mapping do not map the TID column thats it.

Elmozamil - why complicate a simple scenario and use Scripts

---

My Blog    |      Ask Me     |      SSIS Basics     

Answer 4

Hi,

below is the screen shot of the package. I have used flat files instead of tables.

Team Table Flat file for Team table

Member Table flat file for Member table.

Team Member flat file for Team Member table.

Thanks,

Piyush Dhameja. 

Answer 5

hi,

i have two csv file team.csv and member.csv  as shown below

team.csv                member.csv

tname, teamsize                 mnane, tname
team1,       5                            m1,    team2
team2,       5                            m2,    team2 
team3,       7                            m1,    team1

i want to create package which load data from these csv  file into following table at same time

team table          member table           team_member

tid(pk) tname  mid          mid(pk)  mname          id(pk)    tid(fk1)   mid(fk2)       
1        team1   300          100       m1                  1          1             300     
2        team2   200          200       m2                  2          2             200     
3        team3   200          300       m1                  3          3             200     

how can achieve this functionality through SSIS

plz help

Answer 6

You create two flat file connection managers to both .csv files.
In the SSIS dataflow, put two flat file sources to read the data.
After each source, put a SORT component to sort on tname. (warning: this can have performance issues if your csv files contain a lot of rows).
Combine both data sources with a MERGE JOIN, by joining on the tname column.

Write the results to the destination table.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 7

baba,

I have replied to your question earlier as well:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/692a231a-5ace-4896-93fc-65c7ef2951b3/#3795f22f-b57d-4b66-8006-c82f3b10a78e

This is the same requirement with minor changes. It would be good if you refer the above link or continue in the same.

---

My Blog    |      Ask Me     |      SSIS Basics     

Answer 8

baba,

I have replied to your question earlier as well:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/692a231a-5ace-4896-93fc-65c7ef2951b3/#3795f22f-b57d-4b66-8006-c82f3b10a78e

This is the same requirement with minor changes. It would be good if you refer the above link or continue in the same.

---

My Blog    |      Ask Me     |      SSIS Basics     

hi,

that problem was solved,

but this case is different

Answer 9

team table          member table           team_member

tid(pk) tname  mid          mid(pk)  mname          id(pk)    tid(fk1)   mid(fk2)       
1        team1   300          100       m1                  1          1             300     
2        team2   200          200       m2                  2          2             200     
3        team3   200          300       m1                  3          3             200     

how can achieve this functionality through SSIS

plz help

Baba, You have just given the input and output, spoke nothig about the logic?
Team Table: What is mid and how is it to be calculated?

Member table-from where are the PKs getting populated as they are no where in the source table?

how is the 3rd table being created? what do we join with what?

Un less these answers are provided you cannot proceed.

---

My Blog    |      Ask Me     |      SSIS Basics     

Answer 10

team table          member table           team_member

tid(pk) tname  mid          mid(pk)  mname          id(pk)    tid(fk1)   mid(fk2)       
1        team1   300          100       m1                  1          1             300     
2        team2   200          200       m2                  2          2             200     
3        team3   200          300       m1                  3          3             200     

how can achieve this functionality through SSIS

plz help

Baba, You have just given the input and output, spoke nothig about the logic?
Team Table: What is mid and how is it to be calculated?

Member table-from where are the PKs getting populated as they are no where in the source table?

how is the 3rd table being created? what do we join with what?

Un less these answers are provided you cannot proceed.

---

My Blog    |      Ask Me     |      SSIS Basics     

hi,

tid and mid is auto generated,  when data is load as data is load from csv file to table

Answer 11

That explanation is not enough. We are not seeing what you are seening we are new to your process.

How is mid of teamtable getting populated? you say they are autogenerated, should the ETL leave them unmapped?

In the 2nd table the MID is set to identity? with identity seed as 100 and identity increment as 100?

In the 3rd table how are where are each columns getting the value from??

---

My Blog    |      Ask Me     |      SSIS Basics