“Cannot insert duplicate key row” -error in Transform.Common

February 5, 2020 Posted by Alexander Axberg

I recently ran into the following error in the Transform-job in Data Warehouse:

An error countered while attempting to execute ETL Module:
 ETL process type: Transform
 Batch ID: 5601
 Module name: TransformIncidentDim
 Message: ErrorNumber=”2601″ Message=”Cannot insert duplicate key row in object ‘dbo.IncidentDim’ with unique index ‘UniqueIndex’.” Severity=”14″ State=”1″ ProcedureName=”TransformIncidentDimProc” LineNumber=”163″ Task=”Inserting into Dimension”

The error occures when the transform job was trying to instart a specific Incident into the dbo.IncidentDim table in the DWRepository database.
The source of the error was that the specific category in this incident, existed 2 times in the IncidentClassification table, but I guess the same thing could happen to the support groups or any other enum in the incident class.

Solution:

  1. Open the IncidentClassification table in the DWRepository database, and look for enums with duplicate rows (but with different classificationID) with this query:
    SELECT count(enumTypeId) as ‘Count’ ,enumTypeId FROM IncidentClassification GROUP BY enumTypeId
  2. Look for a row where the count number is greater than 1. When found, copy the enumTypeId value and run this query:
    SELECT * FROM IncidentClassification WHERE enumTypeID = ‘<enumtypeID value>’
  3. To decide which duplicate row to remove, run a query against DWDataMart to see which one of the rows are used.
    Run this query against DWDataMart for each IncidentClassificationID above:
    SELECT * FROM IncidentDimvw WHERE Incidentclassification_IncidentClassificationID = ‘<IncidentclassificationID value>’
  4. For the row/rows in #2 that are not used in DWDataMart, delete them from DWRepository by using each the of IncidentClassificationIDs so only one row remains:
    DELETE FROM IncidentClassification WHERE IncidentClassificationID = ‘<IncidentclassificationID value>’
  5. Rerun the Transform.Common job, and it should complete successfully.

Leave a Reply

Your email address will not be published. Required fields are marked *

*