SSIS- fetch the identity value generated in Destination INSERT

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    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]

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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: 147

    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: 258955

    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: 27501

    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

    set string = 'row 4'

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

    where id=4

    Select * From #test

    select * from #hist

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

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

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