How to get auto generated value of any table through SSIS

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

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


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.


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.



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

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


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.

Hope this Helps

Thank you for posting at Dotnetfunda

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

Login to post response