[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL server to Oracle - DTS error

Posted on 2010-03-31
5
Medium Priority
?
598 Views
Last Modified: 2013-11-30
I run a package and it continually fails...

I get this error from job history in Enterprise Manager
Executed as user: INDIANA\LogShippingUserSCC. ...Step_DTSDataPumpTask_1   DTSRun OnError:  DTSStep_DTSDataPumpTask_1, Error = -2147217873 (80040E2F)      Error string:  Error at Destination for Row number 162. Errors encountered so far in this task: 1.      Error source:  DTS Data Pump      Help file:        Help context:  0      Error Detail Records:      Error:  -2147217873 (80040E2F); Provider Error:  1 (1)      Error string:  [Microsoft][ODBC driver for Oracle][Oracle]ORA-00001: unique constraint (BOS_ADMINIS.PK_AMS_ATPM_MOVEMENT) violated      Error source:  Microsoft OLE DB Provider for ODBC Drivers      Help file:        Help context:  0      DTSRun OnError:  DTSStep_DTSDataPumpTask_1, Error = -2147213206 (8004206A)      Error string:  The number of failing rows exceeds the maximum specified.      Error source:  Microsoft Data Transformation Services (DTS) Data Pump      Help file:  sqldts80.hlp      Help context:  0      Error Detail Records:      Error:  -...  Process Exit Code 1.  The step failed.
0
Comment
Question by:GlobaLevel
  • 3
  • 2
5 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29246222
You are trying to insert a row that violates a unique constraint.  In this case the PK of the table (BOS_ADMINIS.PK_AMS_ATPM_MOVEMENT).
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 29249236
how would I fix that..
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29249949
Don't insert duplicate primary key values.

I'm not a DTS person so I'm not exactly sure how to limit the data load with that tool but if you write SQL to do it:  You would need to put some sort of a where clause in the select to not copy over the rows already loaded.

0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 29254934
okay cause I need to write a statement that will go out and check and correctly add the right values...
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 29336866
What is the primary key on the Oracle table?  Hopefully you can add some value to DTS to only process 'new' records.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question