Tuesday, February 26, 2013 2:57 PM
I have been started using SSIS and encountered a big issue.
I am using XML source and going to upload result into columns in MSSQL(thanks to OLE DB destination).
I set up 'keep identity' and try also on MSSQL side by adding 'identity(1,1). What is the issue for me?
1. When I first time upload data is alright but when I was going to upload the same data again SSIS is trying upload data with the same ID.
Is it possible to upload data later but with different id into tables?
Tuesday, February 26, 2013 4:31 PMYou need to uncheck 'keep identity' - in such case SQL Server will generate new sequential ID for the identity column.
- Proposed As Answer by SSISJoostMicrosoft Community Contributor, Moderator Tuesday, February 26, 2013 7:48 PM
Tuesday, February 26, 2013 6:23 PMYes I did that way but second time one I try to upload data again SQL is generating the same sequential ID second time so I have duplicated records.
Tuesday, February 26, 2013 7:26 PM
Are you dropping and creating table between the loads or truncating it?
If yes, second time you create it you should use identity(<new start number>,1)
Tuesday, February 26, 2013 7:50 PMModerator
SQL Server will not generate duplicate id's.... unless you are messing with the identity with for example a reseed http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/
And if you select/check Keep Identity then it ill use the ID's from SSIS instead of the identity of SQL Server. SO uncheck like Piotr suggested.
- Edited by SSISJoostMicrosoft Community Contributor, Moderator Tuesday, February 26, 2013 7:52 PM
Tuesday, February 26, 2013 9:51 PM
Thanks for replay
I do not delete data from table. Most of the time I am adding a new data to tables.
Basically I have 6 tables from XML source. Every node go to different table
like for example
CREATE TABLE [dbo].[a_catalogItem](CREATE TABLE [dbo].[a_ws_item](
[CatalogItem_id] nvarchar(max) NOT NULL,
[catalogidentifier] [nvarchar](max) NULL)
[ws_item_id] [nvarchar](max) NOT NULL,
[isSRI] [nvarchar](max) NULL
) ON [PRIMARY]
First upload is really good because I have good relationship between table but when I am adding second upload SSIS adding second time the same ID's. In addition I also tried to set up identity on SQL server side.
The idea is that this process will be automatic.
I cant use reset table identity because upload new data can always be differnet
I hope does it make sense.