Post reply

Using Identity in data flow after insertion

  • October 30, 2008 at 11:57 am

    #124124

    Hi all,

    Just curious to know how some of you would handle this:

    I have a package in which in my data flow task I need to know the primary key of newly inserted records in my SQL DB (it's an identity column). Let's call it ID. Here's how I did it. I used a script component as a transformation. In the script component I used ADO.NET (insert into statement) to insert the record. Then right after inserting, I used a select statement in that same script component to get the ID of the record I just inserted. I then set one of my output fields equal this value. Then the task is done.

    Now I have the ID for each record and can use it in the next data flow task.

    What I'm curious is, has anyone found a better way to accomplish this than that? Maybe one that doesn't use any code? just trying to find the most efficient way to do this.

    Thanks,

    Strick

  • November 1, 2008 at 7:30 am

    #892709

    Yes, there is. Check out the following stored procedure:

    CREATE PROC [usp_Insert] @insert_value VARCHAR(250)

    AS

    INSERT INTO [MyTable] ( [table_value] ) VALUES ( @insert_value );

    SELECT IDENT_CURRENT( '[MyTable]' );

    GO

    ---

    This is much faster because it queries the current identity from the table meta data, not the table data. Just make sure your key is marked as "IDENTITY".

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

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

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