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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy