Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS- keep identity

Proposed SSIS- keep identity

  • Tuesday, February 26, 2013 2:57 PM
     
     
    Sign In to Vote
    0

    Hello

    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?

     

All Replies

  • Tuesday, February 26, 2013 4:31 PM
     
     Proposed
    Sign In to Vote
    0
    You need to uncheck 'keep identity' - in such case SQL Server will generate new sequential ID for the identity column.
     
  • Tuesday, February 26, 2013 6:23 PM
     
     
    Sign In to Vote
    0
    Yes 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
     
     
    Sign In to Vote
    0

    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 PM
    Moderator
     
     
    Sign In to Vote
    0

    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.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


     
  • Tuesday, February 26, 2013 9:51 PM
     
     
    Sign In to Vote
    0

    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](
    [CatalogItem_id] nvarchar(max) NOT NULL,
    [catalogidentifier] [nvarchar](max) NULL)

    CREATE TABLE [dbo].[a_ws_item](
    [ws_item_id] [nvarchar](max) NOT NULL,
    [isSRI] [nvarchar](max) NULL
    ) ON [PRIMARY]

    GO


    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.

    Regards 

    Adrian