none
Hresult: 0x80004005 Description: "Cannot insert duplicate key row RRS feed

  • Question

  • Good Morning All,

    I have a package and it loads data from flat files into the SQL SERVER table. When i execute the package i'm getting the following error:

    [Insert AgeOfAciveCaseLoadDetail [194]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".

    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot insert duplicate key row in object 'dbo.AgeOfActiveDetail' with unique index 'idx_AgeOfActiveDetail'. The duplicate key value is (Oct 10 2011 12:00AM, 49, 15, 05175048, 175048).".

    I tried using the following link to resolve the problem but no luck. (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4a50e5e4-be22-47f9-a32b-30285e30d975)

    In the package i have a conditional split operator named as validation, can any one please tell how to validate for a duplicate key error. Please any help or advice is greatly appreciated,

    Thanks


    SV

    Thursday, December 20, 2012 3:27 PM

Answers

  • From the destination table.  And set your destination to redirect row on error.  The example I linked to shows you all that

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by saivenkat77 Friday, October 2, 2015 1:00 PM
    Thursday, December 20, 2012 6:53 PM
  • Thanks Cuck now the package is runningh w/o errors, but the strange thing that i see is the same number of records are getting loaded into the OLD and NEW Destination tables.

    SV

    • Marked as answer by saivenkat77 Friday, October 2, 2015 12:59 PM
    Thursday, December 20, 2012 8:16 PM
  • Hi saivenkat77,

    Please analysis the records in the OLD and New Destination tables, and see if any difference betwwen them, the same number of records may not mean much.

    Thanks,
    Eileen


    Eileen Zhao
    TechNet Community Support

    • Marked as answer by saivenkat77 Friday, October 2, 2015 12:59 PM
    Wednesday, January 2, 2013 7:55 AM

All replies

  • You are trying to insert a record which is violating a unique constraint.  Best bet is to redirect the error rows off at the destination to an error file.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, December 20, 2012 4:24 PM
  • Thanks Chuck, i'm new to SSIS and what operator should i used. Like i mentioned above i have a split condition operator which validates ISNULL, is there a way where i can check for duplicates and what function should i use? Also in my case the message says duplicate key on "The duplicate key value is (Oct 10 2011 12:00AM, 49, 15, 05175048, 175048)." I looked for this particular record in PRODUCTION and all i could see is a single value.

    SV

    Thursday, December 20, 2012 4:36 PM
  • Have a look at this:  It'll show you how to do what I'm talking about

    http://www.rad.pasfu.com/index.php?/archives/23-How-to-handle-Failed-Rows-in-a-Data-Flow.html


    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, December 20, 2012 4:38 PM
  • Thanks Chuck, i went through the link. First of all i have some 10 million records in the flat file that needs to be loaded. Secondly in the the PACKAGE i have the following operators:

    1) Flat File Source

    2)Derived column

    3) 4 lookup operators

    4) Split conditional operator and then the (5) Destination table. My question is in the SPLIT CONDITIONAL operator can i set up a condition to redirect the DUPLICATE rows to a different table?

    Thanks


    SV

    Thursday, December 20, 2012 5:47 PM
  • The duplicate error means that your destination table has 1 record existing and another same record (same keys) is coming again from flat file, causing error. I think you can use Look up task to redirect rows.

    Thanks,


    hsbal

    Thursday, December 20, 2012 5:54 PM
  • You have to redirect the error rows, where you have Destination pull the red precedence to one another table destination or file destination this will redirect all the error records to the other destination.

    Please mark it as helpful if it helps.

    Thanks

    Sumit

    Thursday, December 20, 2012 6:10 PM
  • Thanks Chuck, i went through the link. First of all i have some 10 million records in the flat file that needs to be loaded. Secondly in the the PACKAGE i have the following operators:

    1) Flat File Source

    2)Derived column

    3) 4 lookup operators

    4) Split conditional operator and then the (5) Destination table. My question is in the SPLIT CONDITIONAL operator can i set up a condition to redirect the DUPLICATE rows to a different table?

    Thanks


    SV

    Your only option is to redirect the error rows in the destination. You could try to use a lookup to validate record existence and redirect that, but if the 2 records were in the same uncommitted transaction then you would not find the duplicate record and it would still blow up on the insert.

    There is no way to use the split conditional to do what you are trying to do


    Chuck Pedretti | Magenic – North Region | magenic.com


    Thursday, December 20, 2012 6:22 PM
  • From the attched screen shot should i redirect from the red arrow that is coming from the DERIVED COLUMN operator or from the destination table?


    SV

    Thursday, December 20, 2012 6:46 PM
  • From the destination table.  And set your destination to redirect row on error.  The example I linked to shows you all that

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Marked as answer by saivenkat77 Friday, October 2, 2015 1:00 PM
    Thursday, December 20, 2012 6:53 PM
  • Thanks Cuck now the package is runningh w/o errors, but the strange thing that i see is the same number of records are getting loaded into the OLD and NEW Destination tables.

    SV

    • Marked as answer by saivenkat77 Friday, October 2, 2015 12:59 PM
    Thursday, December 20, 2012 8:16 PM
  • Hi saivenkat77,

    Please analysis the records in the OLD and New Destination tables, and see if any difference betwwen them, the same number of records may not mean much.

    Thanks,
    Eileen


    Eileen Zhao
    TechNet Community Support

    • Marked as answer by saivenkat77 Friday, October 2, 2015 12:59 PM
    Wednesday, January 2, 2013 7:55 AM
  • Hi saivenkat77,

    Please analysis the records in the OLD and New Destination tables, and see if any difference betwwen them, the same number of records may not mean much.

    Thanks,
    Eileen


    Eileen Zhao
    TechNet Community Support

    Thanks every one , 

    SV

    Friday, October 2, 2015 12:59 PM