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



We have 3 - 4 Desktop / Windows Application which has MS Access Database.

This MS Access database has couple of tables like Members, Insurance etc ..!

Through SSIS Package, i am trying to import values from MS Access to SQL Database which is located on Main Server.

Issue :- Tables like Members, Insurance from MS Access while importing to SQL - They have PrimaryKey / identity column for MemberId field, InsuranceId..!

How should i import values from MS Access to SQL Tables which has identity/PrimaryKey columns?
chokkaStudentAsked:
Who is Participating?
 
vdr1620Connect With a Mentor Commented:
0
 
Reza RadConsultant, TrainerCommented:
check the Keep Identity in the ole db destination.
0
 
da-zeroCommented:
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?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
vdr1620Commented:
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
0
 
rmm2001Commented:
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.
0
 
chokkaStudentAuthor Commented:
I am working on Prototype of this package.
0
 
chokkaStudentAuthor Commented:
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
0
 
vdr1620Commented:
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/ 
0
 
chokkaStudentAuthor Commented:
It shows database is readonly.

How to make the database both Read / Write
0
 
chokkaStudentAuthor Commented:
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.
0
 
vdr1620Commented:
Rt click on database --> properties --> options ---> Change Database ReadOnly property From False To True
0
 
chokkaStudentAuthor Commented:
Now, how to add Identity field to column ( Primary Key Field ) through SQL Syntax
0
 
chokkaStudentAuthor Commented:
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.
0
 
vdr1620Commented:
The changes will be permanent... The links above describe step by step procedure..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.