Importing Access table into SQL Server 2005 Express table and adding one field

Hi all,

Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.

Bill

Do not select that identity column in your destination adapter.|||Forgive my ignorance, but i am not sure what you are saying. I am still pretty new with SQL Server.|||

Flanman wrote:

Forgive my ignorance, but i am not sure what you are saying. I am still pretty new with SQL Server.

You are using SSIS, right?|||Never mind. I found what you were saying and it works like a champ. Simple. Thanks a bunch for the help.|||

Flanman wrote:

Never mind. I found what you were saying and it works like a champ. Simple. Thanks a bunch for the help.

Excellent! Glad you got it.

Phil