December 28, 2005 at 8:07 am
Hi there,
I have this Dta Transformationtask, which creates duplicate key and I cannot find out why. If I take the SELECT statement from the SOURCE-tab I get no duplicates. The destination is correct, the transformation is ok, as far as I can see. When I run the complete step, I receive the Duplication Error message. So I delete the primkey from the sourcetable, the step is executed and yes, some entries are duplicated... Some, not all;p until now I have not found why some entries are duplicated, but most are not.
I would appreciate suggestions on how to check each step in the task other than the standard options in DTS
Greetz,
Hans Brouwer
December 28, 2005 at 8:54 am
Hi Hans,
Are you calling a stored procedure or sql script that uses a cursor or counting loop?
It sounds like a CONDITIONAL statement isn't being executed properly or a FETCH is misplaced/missing.
Let meknow what you find,
KP
December 28, 2005 at 9:40 am
The preview button only grabs like 200 records, so you could have duplicates after that. If you can use a select in the source with an ORDER by, then set the commit size (options) to 1 or 10 or something and run it. Then when it fails, you can get an idea of which row is failing.
December 29, 2005 at 1:02 am
Tnx for the responses. Maybe I should have elaborate before:
I do not use the previewbutton, I copy the SQL(SELECT * FROM TABLE) to QA and run it. There are NO duplicate records. I know which records are failing and I am searching for the reason why only those are duplicated. ANd, there is no loop or cursor involved.
My main surprise and problem is, that the SELECT statement, in Query Analyzer, shows NO duplicates, but the INSERT action in the Data Transformationtask in the DTSpackage DOES create duplicates. Again, I know which records are duplicate, it's easy to pick them out, and those records are NOT duplicated when I run only the SELECT statement.
SO my question is: what can cause duplicate INSERTS where the SELECT shows NO duplicates?
Greetz,
Hans Brouwer
December 29, 2005 at 1:43 am
Try first on an emty table if your data transfer still giving dublicate error if does then i had same problem in sqlserver 7 on replication and it was pure
replication process problem we never find out why it happened. try to install
sql server 2005 and try same ting there if it happenes if it does you shoud check your data correctly it can give dublicate if your field has a datetime
data type.
December 29, 2005 at 1:52 am
Tnx for the response.
I am doing this on an empty table. There were DATETIME columns involved; on your posting I removed them from the Task and ran the package again, but the problom remains, on the same records.
It would be nice to test this on SQL2005, but that is not a feasable option. The mentioning of something similar happening in a replicationprocess is interesting and I will try and find, if that will help me in my investigations.
Greetz,
Hans Brouwer
December 29, 2005 at 2:00 am
An other way to test dublicate after you remove primary key condition.
When you have dublicated data if it is not many just remove these dubleicated dta from the main table and try again same process for the rest of data. if dublicates occurs again it should be someting wrong in DTS processing.
With my regards
Atik
December 29, 2005 at 2:37 am
Tnx for responding, I have done just that: removed the primkey and ran the package. Then I checked, and a few records, less than 10, hasd duplicates. Again, the corresponding SELECT statement produced no duplicates, as expected.
Greetz,
Hans Brouwer
December 29, 2005 at 3:42 am
I think I have found what is wrong.
Tnx for answering everyone.
Greetz,
Hans Brouwer
December 29, 2005 at 4:10 am
what was the solution or wrong ?
i think readers would like to know
with my regards
atik
December 29, 2005 at 6:51 am
The query consisted, among others, of an outer join construction, to get data not existing yet. The script is quite large and it so happened, that a restriction was aplied twice, which I saw only while explaining what was happening to a collegue...
This happens a lot: you don't notice something, untill someone is looking with you and asking questions...
Greetz,
Hans Brouwer
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply