Auto Increment ID

Hi,
I'm using SSIS 2005 and I need to increment a value in the package as a source column for use as the primary key field in the destination insert. I also would like to find out the maximum key used in the destination table already so that I can set my counter for this field at the value +1. NB. A restriction is that I can't change the destination PK field to an identity field.
geoffcolesAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Hello geoffcoles,

Why not simply set the ID field as an identity column with an increment of 1?

Regards,

Patrick
0
 
bmatumburaConnect With a Mentor Commented:
Create a SQL function that you can call when inserting the data. The function should return MAX(<id_column>) + 1
0
 
bmatumburaConnect With a Mentor Commented:
The function:-

Replace [Your_Table] and ID_IN_Table with the values you are using for the Tablename and the IDColumn
CREATE  FUNCTION [fn_GetNextID]()
RETURNS INTEGER
AS
BEGIN
DECLARE @Return NVARCHAR(20)
SELECT @Return = (SELECT CAST(Max(ID_IN_Table) AS INTEGER) + 1 AS [NewSeq] FROM [Your_Table])
RETURN (@Return)
END

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
using such a function is not really good, as it WILL fail with multi-use access.
as matthewspatrick indicats, using identity is exactly made for that.

to retrieve the generated ID after the insert:
SELECT SCOPE_IDENTIY()

Open in new window

0
 
bmatumburaConnect With a Mentor Commented:
But the user says "A restriction is that I can't change the destination PK field to an identity field..."
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, I read that.
still, I "have" to post that it's not good.
0
 
Patrick MatthewsConnect With a Mentor Commented:
geoffcoles,

I admit, I missed the bit where you said you cannot use identity.  That said, like angelIII I still think that your best bet
by far is to use identity.  No other method will work nearly as well.

Regards,

Patrick
0
 
geoffcolesAuthor Commented:
Thank you for all your assistance. I have decided to change my ID field to an identity column. This has led to other design changes in our application but it's definitely for the best. Thanks for all your help.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
good to hear that we could convince you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.