June 4, 2007 at 12:55 pm
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?
July 13, 2007 at 11:26 am
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.
July 13, 2007 at 12:01 pm
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.
July 16, 2007 at 4:57 am
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
July 16, 2007 at 6:56 am
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