Guys..is there any way to reset the identity key using some transformation except using DBCC CHECKIDENT..In my company, DBCC is disabled..so I cannot use it..so is there any other way to achieve this?

If the table isn't very large you can move the data out and back in: select * into mytemp from my_table order by my_key truncate table my_table set identity insert on my_table insert into my_table select * from mytemp order by my_key set identity insert off my_table I the table is larger than you would prefer sitting in a single transaction, you can use BCP to bulk export the data: bcp my_database.dbo.my_table out f:\temp\my_table.tsv -c -t \t -T -S my_server <truncate the table on the server> bcp my_database.dbo.my_table in f:\temp\my_table.tsv -c -t\ -t -T -E -S my_server -b 100000 This will export and use tabs as the delimiter, if you have tabs in your table, use a different character (instead of \t). The -E on the bcp import command tells it to keep identity values.

Havent tried it, but what about selecting this to overwrite the standard identity? Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com