DTS Job failed

[Asgorath]

Hello,

Inicialy i had created a job to transfer all objects between 2 servers (that failed) , then i created 2 new jobs one that transfer all stored procedures (that's work) and another to transfer all tables (that's fails)

I get the following error :

Executed as user: ARES\SYSTEM. ...n OnStart: Copy Data from acessos to [dinefer].[dbo].[acessos] Step DTSRun OnStart: Copy Data from acessos_por_grupo to [dinefer].[dbo].[acessos_por_grupo] Step DTSRun OnStart: Copy Data from acessos2 to [dinefer].[dbo].[acessos2] Step DTSRun OnStart: Copy Data from actividades to [dinefer].[dbo].[actividades] Step DTSRun OnProgress: Copy Data from acessos2 to [dinefer].[dbo].[acessos2] Step; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: Copy Data from acessos2 to [dinefer].[dbo].[acessos2] Step; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: Copy Data from acessos_por_grupo to [dinefer].[dbo].[acessos_por_grupo] Step; 255 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 255 DTSRun OnFinish: Copy Data from acessos_por_grupo to [dinefer].[dbo].[acessos_por_grupo] Step ... Process Exit Code 60. The step failed.

Any suggestion of what's might be causing the failure, thanks.

Regards
Jorge

[vb_dba]

Are you running this DTS as a job, or executing it from the DTS Designer?

[Asgorath]

Hi
Its running has a shechuled job and i looked at the log file and found several errors similar to one below :

Error: -2147467259 (80004005); Provider Error: 2627 (A43)

Error string: Violation of PRIMARY KEY constraint
'PK_actividades_pro_1__55'. Cannot insert duplicate key in object
'actividades_pro'.

Error source: Microsoft OLE DB Provider for SQL Server

Help file:



Help context: 0


Shouldn't the dts job drop, recreate all the tables on destination and then copy all the rows?

Regards
Jorge

[vb_dba]

Not necessarily. It depends on how the DTS was created. I believe that via the DTS Import/Export wizard, there is an option to drop and recreate a table if it already exists. There is also an option to overwrite the existing data (basically truncate the table and insert the new rows), or append the new data to the existing data, which sounds like your case.

I would suggest modifing the DTS and add an SQL task that truncates the tables, or rebuild the package such that the tables are always dropped/recreated or the data always overwrites any existing data.