0

I am troubleshooting a following error for another user. The other user has a very simple SSIS package. His package contains a source and a destination.

The Source is a different database. Lets say A and destination is a different database. Lets say B

The user wants to copy data from table SrcDBTabA to DstDBTabB

In his SSIS he uses data flow task. The data flow task's isolation level is set to Serializable and TransactionOption is Supported.

The the data flow task the OLE DB Source,he gets the data from SrcDBTabA using SQL Query. His Query is

SELECT SourceID, SourceName,SourceLastName FROM SrcDBTabA WHERE STATUS=1

Then in the OLE DB Destination he maps the the source input columns to his table and has the option Table Lock and Check Contraints checked. His FastLoadKeepIndentity is False FastLoadKeepNulls is False FastLoadOptions is TabLock, Check_Constraints

The package runs fine most of the time but sometimes it fails with the error Violation of Primary Key. "Cannot insert dupplicate key row in object with unique index"

Now here is the interesting thing happening in Source database table SrcDBTabA.

This table is gets data from some another database every 15 mins and data is continuously written in Source database. We use lot of NOLOCKS query in this db and while writting to the Source database table SrcDBTabA from another DB, we do use NOLOCKS.

The Source Database table SrcDBTabA never has duplicate Keys, The ID column is set to be primary and is clustered.

The Source DB property of IS Read Committed SnapShot ON is set to FALSE.

One thing we thought that this error might occur is due to dirtypages

I did read the below reason on the web. The link is Same Data Read Twice

BUT I am not able to understand how come in SSIS we can read a duplicate primary key values when we are not using NO Lock in the Select query and its isolation level is set to Serialized

I am not able to give an answer on this and neither i am able to reproduce this issue in QA.

Any thoughts or reasoning

Ranjeet Choudhary
  • 1
  • 2
  • 2
    Please edit the question and leave only the relevant information. Although duplicate errors typically mean just that. Either the specified value already existed in the target table, or the source query generated duplicates. The error message should contain the values that caused the error. Please don't post excerpts out of context either, at least post the link to them. Finally, NOLOCK isn't a "go fast" switch, it says "I'm OK with reading dirty data". Don't use it unless you know what i – Panagiotis Kanavos Mar 16 '16 at 15:09
  • But still I am not able to understand, if the source does not have duplicate data then how does it comes in SSIS – Ranjeet Choudhary Mar 16 '16 at 15:31
  • You question is too long. do you process data before posting to target table? How do you generate ID? is it IDENTITY? do you clear table before posting data? – FLICKER Mar 16 '16 at 16:57
  • No processing is done before posting to target table. The ID's are generated in the source system automatically via identity. The destination table is not cleared before posting the data – Ranjeet Choudhary Mar 16 '16 at 17:11

0 Answers0