SSIS Export from MS Access to SQL Table which has identity column

check the Keep Identity in the ole db destination.

Do you want the same primary key values in SQL Server then the ones you have in Access? Why don't you let SQL Server generate new values?

There are two options
1. If you want to insert identity values from MS Access to SQL table, Then Map the columns in the destination and Check the box With KEEP IDENTITY in the OLE DB Destination
2. If don't want to insert identity Values, Create an Identity column in your SQL server Table and Then in the Column Mapping in destination ..DO NOT map the Column with IDENTITY Column..Leave it empty...While inserting Values SQL Server will generate its own Identity Values

Is this a one time thing or something you'll be doing a lot?

Do the access databases have the same autonumber values in the Membership/Insurance etc tables or do they differ between desktop? (ex would Membership row 1 in Desktop App 1 be "Bill Smith" and Membership row 1 in Desktop App 2 be "Jane Smith").

If they are like this, the "Keep Identity" box will only work for the first application import and then break for the other ones since that key already exist and you'd lose Desktop 2's values.

^ That's assuming you have an identity column set on your sql tables.

If you have no identity column set on your sql tables then you can insert whatever you want. Just be careful because of the case when duplicate auto nums across applications exist (see above). You'll run into issues when you try to put a primary key/foreign keys on the tables.

If that's true...one approach you could take would be to put a derived column after your Access source in your package. Make the value of "NewKey" be AccessKey * 10000. Then when you insert into your destination table map the "NewKey" to the primary key field and the others normally. Import your other tables the same way ... adding a derived column to be the AcessKey * 10000 and other auto num key fields you have on the table. For each desktop you'll want to make the multiplier be different so you don't overlap data.

I am working on Prototype of this package.

Now, i am facing the issue.

I have exported 10 Tables with Records from MS Access to SQL 2005 Express database.

While exporting, i raised the request for Table creation in SQL 2005 Express.

So, Tables are created and records are inserted to SQL 2005 Express Edition - Database.

Now, these 10 tables has ID Column which i need to set it for Primary Key + Identity feature.

Technically i need to modify the design of the Table.

I am not able to redesign the Table.

Error.bmp

Read more about it before you change the options

Go To Tool --> options-->Designers --> Uncheck the option Prevent the saving Changes that require table recreation

I would suggest to use SQL script instead to change the table Defintions

ALTER Table TableName
ALTER COLUMN ADD Constraint Constraint Name Primary Key (ColumnName)

Ref : http://blog.sqlauthority.com/2007/02/05/sql-server-primary-key-constraints-and-unique-key-constraints/ 

It shows database is readonly.

How to make the database both Read / Write

Msg 3906, Level 16, State 1, Line 1
Failed to update database "C:\ENROLLMENT FORM\ENROLLMENTDB.MDF" because the database is read-only.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Rt click on database --> properties --> options ---> Change Database ReadOnly property From False To True

Now, how to add Identity field to column ( Primary Key Field ) through SQL Syntax

My Question

To an Existing Table

To an Existing Column which has Primary Key.

How should i SET Identity feature. I want to set Idenity permanently.

The changes will be permanent... The links above describe step by step procedure..