Search

DQS_Projects table failing with unique key constraint error by TreyColey

Active

1
0
Sign in
to vote
Type: Bug
ID: 780603
Opened: 3/4/2013 10:58:43 AM
Access Restriction: Public
0
Workaround(s)
0
User(s) can reproduce this bug
We are running the most recent version of SQL 2012 DQS (2011.110.3339.0) and I am running into errors. Unfortunately the errors are not being reported back to the SSIS package so the errors are not resulting in a failure.

Background:
DQS creates a set of tables for each knowledge base and each project (execution instance).

Here is a knowledge base table for CACH. (format = <Database>.<TableName>.<Table>)

DQS_MAIN.KnowledgeBase1000001.V_B_DOMAINS

Here is a project (execution instance) table for CACH. (format = <Database>.<TableName>.<Table>)

DQS_Projects.DQProject1000089.X_DOMAIN_CORRECTION_PROFILING



After waiting several hours for a 100K set of rows to load, I checked the DQServer.DQS_MAIN.log file to find that there was a unique key violation happening in two of the project tables involved in the current execution. The SSIS package never failed.
2/27/2013 5:03:03 PM|[]|12|ERROR|PUID|Microsoft.Ssdqs.Cleansing.Correction.Service.ProfilingCorrectionNotifications|

Failed committing correction profiling data for domain id 1057 in inverted range 1281916-1294447:

Violation of UNIQUE KEY constraint 'UNQ_X_RECORD_CORRECTION_PROFILING_RECORD'.
Cannot insert duplicate key in object 'DQProject1000089.X_RECORD_CORRECTION_PROFILING'.
The duplicate key value is (1024633, 1058).;

Violation of UNIQUE KEY constraint 'UNQ_X_DOMAIN_CORRECTION_PROFILING'.
Cannot insert duplicate key in object 'DQProject1000089.X_DOMAIN_CORRECTION_PROFILING'.
The duplicate key value is (1058, 1294447).;

The statement has been terminated.;
The statement has been terminated.

System.Data.SqlClient.SqlException (0x80131904):
Violation of UNIQUE KEY constraint 'UNQ_X_RECORD_CORRECTION_PROFILING_RECORD'.
Cannot insert duplicate key in object 'DQProject1000089.X_RECORD_CORRECTION_PROFILING'.
The duplicate key value is (1024633, 1058).;

Violation of UNIQUE KEY constraint 'UNQ_X_DOMAIN_CORRECTION_PROFILING'.
Cannot insert duplicate key in object 'DQProject1000089.X_DOMAIN_CORRECTION_PROFILING'.
The duplicate key value is (1058, 1294447).;

The statement has been terminated.;
The statement has been terminated.;



The domain associated with this error is “ExistingCOBCode”. The rules being applied to this column are:
Length is equal to 1
Value is numeric
Value is in 1,2,3,0

99, 104 records were successfully processed before this error appeared. I cannot imagine what I am doing to cause this error. I believe that this is a bug with DQS.
Details (expand)

Product Language

English

Version

SQL Server 2012 - Developer Edition

Category

Data Quality Services

Operating System

Windows Server 2008 R2 Standard

Operating System Language

US English

Steps to Reproduce

Create a 52 domain knowledge base.

Set up SSIS DQS cleansing component.

Run 100,000 rows thruogh the DQS cleansing component

Notes:

4 SSIS DQS components are running in parallel in the data flow task. This was modeled after the DQSPerformanceBestPractices.docx (see the section named "Cleansing with SSIS")

The default buffer size for the data flow task was set to the maximum (104857600) in an attempt to improve performance. This was based on advice from MS in the following article.

http://connect.microsoft.com/SQLServer/feedback/details/713837/data-quality-services-dqs-cleansing-component-performance-too-slow

Actual Results

2/27/2013 5:03:03 PM|[]|12|ERROR|PUID|Microsoft.Ssdqs.Cleansing.Correction.Service.ProfilingCorrectionNotifications|

Failed committing correction profiling data for domain id 1057 in inverted range 1281916-1294447:

Violation of UNIQUE KEY constraint 'UNQ_X_RECORD_CORRECTION_PROFILING_RECORD'.
Cannot insert duplicate key in object 'DQProject1000089.X_RECORD_CORRECTION_PROFILING'.
The duplicate key value is (1024633, 1058).;

Violation of UNIQUE KEY constraint 'UNQ_X_DOMAIN_CORRECTION_PROFILING'.
Cannot insert duplicate key in object 'DQProject1000089.X_DOMAIN_CORRECTION_PROFILING'.
The duplicate key value is (1058, 1294447).;

The statement has been terminated.;
The statement has been terminated.

System.Data.SqlClient.SqlException (0x80131904):
Violation of UNIQUE KEY constraint 'UNQ_X_RECORD_CORRECTION_PROFILING_RECORD'.
Cannot insert duplicate key in object 'DQProject1000089.X_RECORD_CORRECTION_PROFILING'.
The duplicate key value is (1024633, 1058).;

Violation of UNIQUE KEY constraint 'UNQ_X_DOMAIN_CORRECTION_PROFILING'.
Cannot insert duplicate key in object 'DQProject1000089.X_DOMAIN_CORRECTION_PROFILING'.
The duplicate key value is (1058, 1294447).;

The statement has been terminated.;
The statement has been terminated.;

Expected Results

Expected all 100,000 rows to go through the SSIS DQS component without error. However, if there was an error in DQS, the expectation was that this error would cause the SSIS component to fail.

The least expected result was a DQS error that was never reported back to the DQS cleansing component in the SSIS package.

Platform

X64

Virtualization

Other (e.g. VM Ware, specify in Description)
File Attachments
File Name Submitted By Submitted On File Size  
DQS Issue Details.zip (restricted) 3/4/2013 -
DQServerLog.DQS_MAIN.log (restricted) 3/4/2013 -
Validate Membership DQS.zip (restricted) 3/4/2013 -
Sign in to post a comment.
Sign in to post a workaround.