Alternative to Identity Help.

I have a SSIS package that imports an Excel file using Visual Basic 2005 into my SQL 2005 database. All the fields are the same in the DB and xls. The package runs with no problem but I need one of the fields to be autoincrement. I tried to set up the properties of one of my fields "ID" to be an Identity. This didn't seem to work at all. There are about 1300 records in the DB so far with the last "ID" number being 10001415. Before now, the numbers were inputed manually. I want the "ID" to be assigned when the SSIS package imports the xls file.

Any help is very appreciated.

See if this helps:
http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/|||

Not really. I opened the SQL Server Management Studio and tried to edit the DTSX package. I couldn't find any of those options you mention in the article. I am not that familiar with SSIS. I just used the wizard to create it.

|||

I think all you have to do is to set a column in the target table to be an Identity (Identity Specification = Yes).

BTW, to edit a ssis package you have to use Business Intelligence Studio.

|||

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column. I tried creating a new column whith the Is Identity set to Yes with the appropriate seed and increment, however, I have records that have been deleted and now the numbering is off from the original format. What I mean is I have records numbered 1, 2, 3, 5, 6, 9, 12, etc in the DB in that order. When I create the new column and set the ID, it is unaware that I have records missing and what I end up with is 1, 2, 3, 4, 5, 6, etc.

I also tried creating a new SSIS package in order to use surrogate ID's and cannot figure out how to implement that within my import from Excel.

Please help!

|||

ISSOA wrote:

I previously set my column to Identity = Yes. When I run my DTSX package I get an error. It will not let me change a current column to identity and then import new data into the column.

If you want to insert explicit values in a identity colum you have to alter the table:

SET IDENTITY_INSERT <tableName> ON

|||

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

|||

ISSOA wrote:

I set all that up and I still get an error that I have violated the constraints for the column. The column that I am importing from EXCEL is blank. I want it to get a number when it is imported by the DTSX job. When I put a number in that column within the EXCEL sheet the number stays the same. Any ideas?

Don't map that column to the destination column. Leave it out of the Import/Export Wizard and you should get the results you desire.