Package Insert Primary Key - SQL Server Forums

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)

Package Insert Primary Key

Author

Topic

ypod
Starting Member

4 Posts

Posted - 2007-11-07 : 17:46:58

Hi,

I am new to SSIS and still trying to design my first package.
My package doesn't seem to want to complete when I have set an indentity field with an identity seed in my table as a primary key.

If the field is not a primary key, the package runs fine.

Any ideas how to insert records when there is a primary key in the table?

In another instance, I am actually trying to fill the primary key with a value from flat file and that also does not work.

Any ideas would be appreciated. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-07 : 17:49:37

If you are using the identity option, then you need to exclude that column from the insert list so that SQL Server can provide this value for you.

For instance:
table1 - column1 identity, column2 varchar(5)

insert into table1 (column2)
values ('tara')

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ypod
Starting Member

4 Posts

Posted - 2007-11-07 : 17:53:08

That is if I am inserting via SQL Statement right? But I have created a data flow task to perform some string functions and then done the mappings to a data destination object within the data flow task.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 18:04:24

when you write the query for the source data, make sure to not include a column for the destinate identity field. then when you get to the edit mapping click it. make sure that destination identity field is not mapped to any source field and the enable identity insert checkbox is not checked.

ypod
Starting Member

4 Posts

Posted - 2007-11-07 : 18:11:50

Write the query?

To be more specific, my package looks as such:

1. Flat File Source linking to a flat file
2. Derived Column Object that performs string functions
3. SQL Server Destination Object that performs the mappings to a SQL Server table

I can't see any enable identity insert checkbox.

Sorry I don't mean to be difficult, just trying to figure it out.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-09 : 08:59:00

You don't do identity insert. Because how do you know your flat file will have that key unique?
This is the solution the others have suggested, i.e. skipping that field.

On the other hand, if you trust, or you must use the key from the flat file, then you should define your destination table without that column to be the PK.

ypod
Starting Member

4 Posts

Posted - 2007-11-11 : 17:06:08

I have tried to leave that column out but all the boxes turn yellow when I start debugging and it seems to process forever and never finish. Also, when checking the table, no records have been inserted into the table.

Only when I specify the field as non primary does the package ever finish.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-11 : 17:48:31

Import the table so that it finishes, then add the primary key after.

--make sure the field is all unique values
Select COUNT(FieldName), Count(DISTINCT FieldName)
FROM TABLE
--if the values match add that field as Primary Key.

ALTER TABLE [TableName] ADD PRIMARY KEY (FieldName)

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources