Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scanestri
Starting Member
4 Posts |
 Posted - 2008-09-02 : 08:34:48
|
| Hello, I have a DTS that run like a sql job.It gets data from a AS400's db, transform and copy all records in a SQL's db at least once a day. Note: the SQL tables are cleared when the DTS start.All work fine for just 2-3 days, but at next running time the job fails with an error like this:"violation of primary key constraint [..] cannot insert duplicate key in object 'MYTABLE'".I verified that the job run the Transform Data Task to copy the records normally, and sometimes tries to execute the operation again, causing the job failure.Can you help me?Thanks in advanceSimone |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-02 : 16:28:19
|
| Do you have identity column as primary key? How are you handling it? |
 |
|
|
scanestri
Starting Member
4 Posts |
Posted - 2008-09-04 : 04:10:56
|
| No, I don't use an Identity Column, just a Primary Key defined as:CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED ([MYID]) ON [PRIMARY] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 04:13:32
|
| Are you sure you've no duplicate values coming for this column from the source? |
|
|
|
scanestri
Starting Member
4 Posts |
Posted - 2008-09-04 : 04:39:39
|
| Yes, data in AS400 are stored in a table with no allowed duplicates. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 05:11:12
|
| that may be because the previous dts run dumped some data to your destination table before it failed. and next run is also trying to dump same data once again causing this error. could you remove all records from destination table which were dumped by previous dts run and try again. |
|
|
|
scanestri
Starting Member
4 Posts |
Posted - 2008-09-04 : 05:48:11
|
| The destination table, just after job failing, is always empty; it's so strange! |
|
|
|
|
|
|
DTS job fails during Import from AS400