none
OLEDB Command how to get the value of OUTPUT Clause of Insert Statement RRS feed

  • Question

  • Hi,

    I have 2 tables, the Identity column in Table1 is foreign key in Table2. I need the parent table identity column value to insert the records into child table.So iam using OLEDB command in the SSIS Data flow task to insert the record to parent table after insert i would like to get the identity column value for that inserted column. I found that using OUTPUT Clause of the Insert statement we can get the identity column value. I am not able to figure it out how to get the column value and store in a variable since i need it down the line. Can any one help me in these regard.

     

    OLEDB command:

    insert into Table1(col2,col3,col4) output inserted.col1 values(?,?,?)

    col1 is identity column

    in the column mapping iam able to set the values for the inputs. Now how can i get the col1 to a variable.Or is there any other method to work out.

    Any help?

     

    Thanks


    latha
    Friday, August 26, 2011 2:00 AM

Answers


  • Here's one way - just know it'll suck at runtime.  The OLE DB Command is not a good performer.
    First, you need to create a stored procedure to do your insert.  Something like:
    CREATE PROCEDURE InsertIntoTable1
     @col1 AS INT OUTPUT,
     @col2 AS INT,
     @col3 AS INT,
     @col4 AS INT
    AS
    INSERT INTO Table1 (col2, col3, col4) VALUES (@col2, @col3, @col4)
    SET @col1 = SCOPE_IDENTITY()
    
    Then, use a Derived Column prior to the OLE DB Command to add a column to your flow for col1 - the OLE DB Command has to have somewhere to put the value.
    In your OLE DB Command, use a command of EXEC InsertIntoTable1 ? OUTPUT, ?, ?, ?, and map the columns.

    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Eileen Zhao Thursday, September 1, 2011 5:52 AM
    Friday, August 26, 2011 6:19 PM
    Moderator

All replies

  • Hi Latha,

    To achieve this follow the steps given below.

    1. declare a variable of type object scoped at package level.

    2. Configure your Execute SQL Task Control.

              a) Select "Full Result Set" as Result set value.

              b) in the result set tab select the name of the variable u created in Variable Name and in the result name type 0.

    3) Save the Changes     

    Now you have the Col1 values saved in the variable.

    Friday, August 26, 2011 10:01 AM
  • You can execute this with the SQL Execute Task.

    in the statement replace your output inserted.col1 with '?'
    In the parametermapping specify in parametermapping the variable and change the direction to Output.

    Friday, August 26, 2011 1:44 PM
  • Is there a way to perform it in the OLEDB Command since i need the Col1 value to perform Merge Join after the OLEDB Transformation.So, i am not able to come out of the Data Flow Task and use Execute SQL Task.

     

    Thanks


    latha
    Friday, August 26, 2011 1:51 PM

  • Here's one way - just know it'll suck at runtime.  The OLE DB Command is not a good performer.
    First, you need to create a stored procedure to do your insert.  Something like:
    CREATE PROCEDURE InsertIntoTable1
     @col1 AS INT OUTPUT,
     @col2 AS INT,
     @col3 AS INT,
     @col4 AS INT
    AS
    INSERT INTO Table1 (col2, col3, col4) VALUES (@col2, @col3, @col4)
    SET @col1 = SCOPE_IDENTITY()
    
    Then, use a Derived Column prior to the OLE DB Command to add a column to your flow for col1 - the OLE DB Command has to have somewhere to put the value.
    In your OLE DB Command, use a command of EXEC InsertIntoTable1 ? OUTPUT, ?, ?, ?, and map the columns.

    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Eileen Zhao Thursday, September 1, 2011 5:52 AM
    Friday, August 26, 2011 6:19 PM
    Moderator
  • Did you get answer? i have same senario

    Monday, September 19, 2016 8:49 PM