Adding an identity column in SSIS

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  October 16, 2009 at 11:04 am

  #136690

  Hi all,

  I have a requirement to implement a surrogate key column in my SSIS package.

  Actually let me explain in detail:

  I have a DeptNO Column which I have to incerment everytime there is a New record found ( I have to lookup on DepName and increment the DepNo for every new DepNAme found)

  Now I tried to do this by using Execute SQL task to get the Last Max key from the DEPT table and then if its a new row from the lookup I was adding 1 to the Variable that stores the Max DEPNO key, but its not working, its giving all Depno the same value.

  Any help on this.

  Tahnks in advance

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  October 17, 2009 at 4:15 am

  #1067312

  So you are adding data to an existing table using SSIS and it is in this table that you would like to auto-increment department?

  Why not use an IDENTITY field on the department table? Then SSIS is not really involved.

  ---