Solved

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

Posted on 2010-08-20
14
1,047 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

680 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