primary key problem in SSIS package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

primary key problem in SSIS package

Author

Topic

evmisu
Starting Member

17 Posts

Posted - 2009-01-18 : 23:59:15

Hi all,

I am new to SQL Server 2005, and have a problem in SSIS.

I want to integrate a series of DB format file into my database scheet. The source .dbf format file have not Primary key row, and the destinition sheet consist a primary key(namely ID). It seems that SSIS would not automatically put new record in the key(ID) row, so my Package cannot functioned.

If I cancel the Primary Key row in the destinition sheet, the SSIS package functioned well.

Please tell me how can I input the source DB without primary key into a destination with primary key. Thank you very much.

Andy sam

Nagaraj
Starting Member

14 Posts

Posted - 2009-01-19 : 00:31:23

Hi Andy,

As primary key maintains uniqueness it doesn't allow you to insert duplicate values in the destination.

so from the group of records (from source which doesn't have primary key) only one record will be inserted into the destination. And when you try to insert a record with the same primary key, it throws an error saying primary key violation and the package gets failed.

If your requirement needs only one record(new record) needs to be inserted into the destination, then you need to inform which (new) record needs to be picked up. You can handle this in the sql query.

Nagaraj.

evmisu
Starting Member

17 Posts

Posted - 2009-01-19 : 01:19:49

Thank you very much Nageraj for you prompt informaion.

My solution is: integrate the source into a sheet without primary key, then use sql query to add a primary key with countrow function, and generate to a new sheet (sql: select * into ).

since this file have to integrated everyday, I wonder if any more direct way to solve this problme with SSIS (like adding some function in the data import flow). My data flow task in the SSIS is "OLE DB source"===>"Data Conversion"===>"OLE DB Destination".

Thank you all.

Nagaraj
Starting Member

14 Posts

Posted - 2009-01-19 : 02:14:14

Use one more auto generated column (with primary Key) in the destination. So that you can transfer the duplicate records to the destination by maintaining uniqueness in the destination.

Nagaraj.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 02:38:11

just adding an identity column in destination and excluding it transformation mapping will be sufficient

evmisu
Starting Member

17 Posts

Posted - 2009-01-19 : 04:44:44

Thank you Nagaraj and Visahk for your answer and your kind help. Please forgive my poor english Expression and understanding. I tried the mentioned apporach and i cannot exclude the transformation mapping(conrespondence) in the destination

I want to do these integration

source file(without Pkey) Destination
ID (primary key)
Rev ---------> Rev
Date ---------> Date
type ---------> type

Nagaraj
Starting Member

14 Posts

Posted - 2009-01-19 : 08:14:20

Evmisu,

As per my understanding from Visakh suggestion is you need to map as follows

NewIDColumn (in Destination) -- Dont map this target column to any source column and make it an identity column so finnaly your mappings looks as follows

NewIDCol
ID --> ID
Rev --> Rev
Date --> Date
Type --> Type

Nagaraj.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 09:24:42

quote:
Originally posted by Nagaraj

Evmisu,

As per my understanding from Visakh suggestion is you need to map as follows

NewIDColumn (in Destination) -- Dont map this target column to any source column and make it an identity column so finnaly your mappings looks as follows

NewIDCol
ID --> ID
Rev --> Rev
Date --> Date
Type --> Type

Nagaraj.

you're right nagaraj. thats exactly what i meant. this will put values from other columns in source to relevant columns in destination and identity column will automatically take auto incremented values.

evmisu
Starting Member

17 Posts

Posted - 2009-01-19 : 22:10:13

Oh my god.

Dear Gentlemen, I finally find my problem. I didn't set the attribute of the primary key as "identity" thus I get failure all the time. how could I did such a stupid thing and weste your valuable time.

Anyway, Thank you veru much, and I begin to like this forum very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources