[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 764
  • Last Modified:

DTS Duplicate Key Error

I have a DTS package that  updates an Item Master table from Oracle to SQL 2000. I'm looking to capture the updates to the Inventory Master in SQL 2000.  My DTS table is just copying the tables.  I'm getting the following error: Error at destination at row number 35465.  Errors encountered so for in this task:1 Duplicate key wass ignored.

1 Solution
Change the package to load a table that is identical to the inventory master, with one change: do not put a primary key on it (put the field in, but don't make it the primary key). Put the data from the original table in it, then execute the package.Then execute the following:

Select Nameofpkfield
from InventoryMasterTable
Group By Nameofpkfield
Having Count(*) > 1

That will show you the duplicates.
Before you run the DTS are you truncating/deleting the SQLServer table?
If not, you will get duplicate errors the second time you run the DTS. IF this is the case, then you need to either dump the contents before inserting, or only insert reocrds not already there.

Several things to do here if there are duplicates in the source :
1. Modify the DTS to select only one record -
modify the SQL to be a SELECT DISTINCT (with maybe MAX() on other cols to ensure only one record per PK - depends on how you want to react to two records with samne 'PK', but different other columns?)
2. Modify the DTS to increase the number of errors allowed. Then have it log errors to an error file, which you can then analyse to determine the problem
3. A trigger on the source table could be used to ignore duplicates / or records which are already in the target. THis would handle either case where the source has duplicates, or where you have already inserted records. You can also have the trigger update any record which are already there.

Depending on the type of task you're using, and if you ise the transform task, you can set an error file to list what are the records causing the problem.
I find this option quite handy when you start having integrity violation or data type conversion error. You can then take appropriate actions as mentioned earlier to fix the problem.

If not using transform task, try setting an error log on the package itself, it might help with bit more accurate message.


Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now