How to get auto generated value of any table through SSIS

Posted by Sksingh under Sql Server on 6/16/2011 | Points: 10 | Views : 3906 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

I am very new to SSIS package and facing problem in that.

Scenario

Suppose i have two tables Student and StudentDetails and both tables have RegistrationNumber column in flat file.
I want to import all flat file data in to Sql Server tables.

How to import

When a Student record from a flat file get insert into Student table in SQL, one autogenerated StudentID get generate which will be identity column as well as primary in Student table and foreign key in StudentDetails table.

Issue

Whenever one record from student flat file get insert in Student table in SQL, then corresponding student details should also get insert into StudentDetails with foreign key , which get generated by Student table.

My Question is how to get auto generated value of Student table through SSIS.

I am able to inner join on both table based on RegistrationNumber but i am not getting StudentID which is autogenerated in Student table.

Regards,
Sunil

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Vuyiswamb on: 7/3/2011 [Member] [MVP] [Administrator] NotApplicable | Points: 25

0	Do you want to execute this single command for two tables? e.g insert into Student ...... insert into StudentDetails values(...,@@identitity ....... if i understand you correctly , you have two tables that are equivalent to SQL tables, and you want to generate a student number from the student number and also carry the student ID for the second insert ? if so , You need to execute the First one "Student" and after that use the variables or if your problem is to pass the variables in SSIS , you count just get the max value from the table and use it as the Student ID to do the second insert. http://www.codeproject.com/KB/database/SSIS-Variable.aspx Hope this Helps Thank you for posting at Dotnetfunda [Administrator] Sksingh, if this helps please login to Mark As Answer. \| Alert Moderator

Do you want to execute this single command for two tables?

e.g


insert into Student ......



insert into StudentDetails  

values(...,@@identitity .......

if i understand you correctly , you have two tables that are equivalent to SQL tables, and you want to generate a student number from the student number and also carry the student ID for the second insert ?

if so ,
You need to execute the First one "Student" and after that use the variables or if your problem is to pass the variables in SSIS , you count just get the max value from the table and use it as the Student ID to do the second insert.

http://www.codeproject.com/KB/database/SSIS-Variable.aspx

Hope this Helps

Thank you for posting at Dotnetfunda
[Administrator]

Sksingh, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts