DTS package returns less records when scheduled

[sqlturbo]

I created a DTS package that copies a table from one database to another. It copies all the rows when I run it manually. If I run it as a scheduled job in MS SQL Server 2000, it does not copy all the rows. I created a exception file to trap the exception and it says

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@@ErrorRow: 10211
"Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Cannot insert duplicate key row in object 'TableName' with unique index 'IndexName'.
Error Help File:
Error Help Context ID:0
@@SourceRow: Not Available
@@DestRow: Not Available

@@ExecutionCompleted

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I cannot find a duplicate entry in that table for the colum with the unique index constraint. And oddly enough, I get this error only when I run it as a scheduled job.

I have digged a lot but I still do not understand this peculiar behaviour.

Any ideas??

TIA

 

[cyno]

When you run the package as scheduled job what is the option you have selected for transformation properties. Did you specify drop and recreate destination table?

Cyno

 

[sqlturbo]

I delete all the records in the destination table before I load it with data.

 

[cyno]

What is the table size. If it is small when you select the preview data are you able to see all the data in the source table. Didn't come across this situation before...

Try using the truncate table instead of delete and see if it works. Run the job under sql services account.See if it works.....

Cyno

 

[sqlturbo]

The table is not big. It has about about 12000 rows.
I can see 200 rows under the preview, which I guess is the default.

To I run the job under sql services account, do I have to run the SQL Server Agent under that account. I currently have it running under System Account.

I have not seen this before either. If the unique constraint prevents insertion of data, it should do it irrespective of how the DTS package is run.

 

[Catadmin]

Do you have "Insert Identity" chosen as an option when you do this? If not, turn it on and see if your scheduled package runs. If so, try turning it off and see what happens.

BTW, I usually create my packages with the Import/Export Wizard, which is where I see this option. If you right click your workflow in the DTS Designer, go to Properties then click options, you should see the Enable Identity Insert check box near the bottom right of the box.

Hope that helps.




Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"