Search code examples
sqlssisoledbauto-increment

Adding Auto Increment column in destination SSIS?

I am creating a package in SSIS and this will insert records based on the look up operation as like below .

Source : View Destination : Table

enter image description here

NOw I have an issue while inserting record i need to insert into a destination column which is not in the source and that column needs to be autoincremented by 1. How to do it here.

Any suggestion is welcome ?

Regards

Solution

  • Let us say the column name is "Id". If column "Id" already has an identity to it, then you're good. If not, then in your destination table you will have to create a new identity column, remove the "Id" column and then rename new column to "Id".

    ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1) ALTER TABLE (yourTable) DROP COLUMN Id EXEC sp_rename 'yourTable.NewColumn', 'Id', 'COLUMN'

    After this in your SSIS destination do not map any column from the source to Id column and the Id column in database will start from value 1 and automatically increment by a value of 1. Hope it helps.