Solved

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

Posted on 2010-08-20
14
1,044 Views
Last Modified: 2013-11-30


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?
0
Comment
Question by:chokka
14 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33490357
check the Keep Identity in the ole db destination.
0
 
LVL 1

Expert Comment

by:da-zero
ID: 33490879
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33492290
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 7

Expert Comment

by:rmm2001
ID: 33496653
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
 

Author Comment

by:chokka
ID: 33631043
I am working on Prototype of this package.
0
 

Author Comment

by:chokka
ID: 33727055
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33727164
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
 

Author Comment

by:chokka
ID: 33727658
It shows database is readonly.

How to make the database both Read / Write
0
 

Author Comment

by:chokka
ID: 33727679
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33729028
Rt click on database --> properties --> options ---> Change Database ReadOnly property From False To True
0
 

Author Comment

by:chokka
ID: 33729340
Now, how to add Identity field to column ( Primary Key Field ) through SQL Syntax
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33729455
0
 

Author Comment

by:chokka
ID: 33729496
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 33730579
The changes will be permanent... The links above describe step by step procedure..
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 - Merge Replication Issue 1 22
optimize stored procedure 6 28
Need help constructing a conditional update query 16 46
SQL server vNext 18 29
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question