selecting an identity

  • i have a table with an int identity pk.

    on my control flow, i execute an sql task to insert a row.

    i think have another execute sql task to select @@identity.  it is aliased and cast to an int.  statement works fine in a query tool, but keeps returning 0 in ssis.  what's the deal, how do i get the id of my last row inserted?

  • You can't. Google "SSIS Surrogate Keys". You can do something similar by first retrieving the MAX(ID) from the Database Table. Store the MaxID in a variable.

    Next, have a Script component that has a "Counter" (Dim Counter as Integer = 1) which gets incremented for each Row. In the "ProcessInputRow" event of the script, add that Counter to the MaxID variable (Row.SurrogateKey = Counter + MaxID).

    Finally, you'll need to insert all the Rows with "Set Identity Insert [Table] On" because you've already generated the Identity value.

  • i will update this thread when i get home and see what i did end up doing, as i did get around this problem.  ok it's friday... hopefully i will update this thread... 

    despite that no competing etl's or processes should be running at the time my etl is running that could interfere with this id, i find the "add 1 to max" a really outdated and inelegant process today.

  • SQL Server 2005 has a new OUTPUT clause which you can use to get Deleted /inserted values .Serach on Output in the SQL Server help or use below link for deatils

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm

  • You can get the identity back with @@Identity, but only if you tell SSIS to not open a new connection for each component.  In your connection manager, set the RetainSameConnection property to True.  This will force SSIS to keep the connection open and re-use it.  Since the connection will remain open, @@Identity will work.

    SSIS is not very good at the whole retrieving of a key like this.  If you give some specifics about what you are trying to do, I may be able to give you a different direction to try.  I have found that programming in SSIS turns out to require learning to think differently.

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

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