Solved

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

Posted on 2010-08-20
14
1,035 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now