How to insert scope_identity from parent table into the child table using SSIS

Question

Hi,

I have a parent table tbl1 which has a primary key. I want to insert data into tbl1 and then the PrimaryKey is inserted with other details into child table which is tbl2.

Example

tbl1

ID    Name

1     Profile

2     Test

3     New

tbl2

ID       tbl1ID    Details

100      1          a new profile added

101      2          testing details

102      3          new details added

So If id 4 is added in tbl1, I want ot insert data against the id 4 in tbl2 and so on...

I want to achieve the above using SSIS.

Thanks in advance for your help.

Aash

---

Aash

Answer 1

Hi,

To achieve this, in execute sql task you can use select scope_identity() function. You can save the value of this into variable. Later insert the value of the id (from variable) into child table.

Thanks,

---

hsbal

Answer 2

Hi Harry.

Have you got an example which I can refer to. I have done the scope_identity in SQL. Wanted to know how to do it in SSIS.

Thanks,

Aash

---

Aash

Answer 3

Insert your header table using a script like ....

INSERT

INTO dbo.TblABC (Name)

VALUES ('Profile')

SELECT @ID = SCOPE_IDENTITY()The @ID is a SSIS variable that will be used in the LOOP for your Detail table , you can use it in a derived column

---

Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 4

Aash,

Unfortunately, I havnt got any example. But Let me try to explain in detail.

step 1. First excecute sql task : This would insert rows in parent table. Just when insert statement finishes, you should run select scope_indentity() to capture the id generated. Save this result set into a variable.

step2: Second execute sql task: the query here would use the variable that the holds the value of the id generated in parent table.

Thanks,

---

hsbal

Answer 5

1- use a script task

2- add a Parameter as input to pass the values of the NAME column

3- Set the result set to single row and add a variable that will play the role of the ID in the RESULT SET Lets call it MyID

3- with in it use

DECLARE @tblName NvarChar(50)
SET @tblName = ?
INSERT INTO dbo.TblABC (Name) 
VALUES (@tblName)
SELECT SCOPE_IDENTITY()

4-now use  MyID in the next option

5- a loop or a DFT that will insert the  DETAIL of the records in the second table

6- good luck

---

Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).