SSIS- fetch the identity value generated in Destination INSERT

Question

Post reply

SSIS- fetch the identity value generated in Destination INSERT

sql_learner29

SSCrazy Eights

Points: 9107
More actions
November 9, 2011 at 10:30 am

#251523

Hi
Is there anyway I can get the identity value generated by the OLE DB destination and use it in UPDATE statement as I have to store the target ID generated after doing each insert.
there is no business key in TRG and this way I can do one to one map in Source and Target data.
I will have to make sure that one insert and one update happens togethter so that i dont mix data....
here is some sample code:
Create table SRC
(
CHILD_id int,
NAME varchar(10),
PARENT_id int,
TARGET_id int
)
insert into SRC
select 11,'ABC',1,NULL
union all
select 22,'DEF',1,NULL
union all
select 33,'XYZ',22,NULL
union all
select 44,'ABC',22,NULL
union all
select 55,'XYZ',44,NULL
union all
select 66,'ABC',44,NULL
Create TABLE TRG
(
TRG_id int identity(1,1),
TRG_NAME varchar(10),
PARENT_TRG_id int
)
select * from SRC
select * from TRG
In this case I have to update the Target_Id in SRC with TRG_id generated in target table.
any help please.
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

You cannot do this in SSIS directly.

But there are some other options:

* use an Execute SQL task to transfer the data. In the INSERT statement, you can use the OUTPUT clause to get your IDs.

* use an OLE DB command to insert the data. Use a stored procedure that inserts one row and gives you the ID back by using @@IDENTITY or SCOPE_IDENTITY. Be aware that this is a very slow solution. See the following article on how to add an output to the OLE DB command: http://www.rad.pasfu.com/index.php?/archives/24-Output-Parameter-of-Stored-Procedure-In-OLE-DB-Command-SSIS.html

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

glebih SSC Enthusiast Points: 154 More actions · Answer 2

Assuming that an UPDATE statement effectively DELETEs and INSERTs a record, can the values not be retreived via OUTPUT ?

IE

UPDATE table

SET Col1 = Val1

OUTPUT inserted.Col1 (should be Val1), deleted.Col1 (should be old value)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

glebih (10/17/2016)
Assuming that an UPDATE statement effectively DELETEs and INSERTs a record, can the values not be retreived via OUTPUT ?
IE
UPDATE table
SET Col1 = Val1
OUTPUT inserted.Col1 (should be Val1), deleted.Col1 (should be old value)

First of all, this question is 5 years old, so I hope the OP already got his answer 😉

Secondly, the question was about inserting rows with SSIS, so there is no actual UPDATE statement.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

MMartin1 One Orange Chip Points: 27795 More actions · Answer 4

glebih (10/17/2016)
Assuming that an UPDATE statement effectively DELETEs and INSERTs a record, can the values not be retreived via OUTPUT ?
IE
UPDATE table
SET Col1 = Val1
OUTPUT inserted.Col1 (should be Val1), deleted.Col1 (should be old value)

You are encouraged to start a new thread when your question differs enough from what the topic of the post is. For now though the answer to your question is yes

Try the following

create table #test(id int, string varchar(30))

insert into #test

values

(1,'row1'),

(2,'row2'),

(3,'row3'),

(4,'row4'),

(5,'row5'),

(6,'row6'),

(7,'row7'),

(8,'row8')

;

create table #hist (id int identity, oldVal varchar(30), newVal varchar(30))

;

update #test

setstring = 'row 4'

output deleted.string, inserted.string into #hist(oldVal, newVal)

whereid=4

Select * From #test

select * from #hist

----------------------------------------------------