SQL Server Performance Forum – Threads Archive

DTS bug

I have a DTS package setup for copy objects, replacing the data… The job report success but there is some data not copied to the destinsation. Sometimes it copies all data, sometimes only half the data. Very bugggy. Is this a known issues in SQL Server 2000 STD, SP 3.0?

---

Its not a bug, but to know the details you should enable package logging.
Use the DTS package log to troubleshoot problems that occurred during the execution of a DTS package. BOL refers:
To enable package logging Open the Data Transformation Services (DTS) package for which you want to create a log.
On the Package menu, click Properties to display the DTS Package Properties dialog box.
Do one of the following:
Save package logs to Microsoft® SQL Server™ by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.
Security Note When possible, use Windows Authentication. Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS Package dialog box, in the Location list, select Meta Data Services.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

package showing successful on the copy objects. But missing 15 rows. How can this NOT be a bug?

---

Does the source and destination have different collation? Are all related tables transfered or could a foreign key constraint triggered? Are the the datatypes and size (say varchar/nvarchar, varchar(30)/varchar(25)) the same on both source and destination? Could you post the results of the DTS package log.

---

I’d also look at the source file. Sometimes text files have hidden characters in them that may mimic line breaks and if sql cant see them, it considers it extra characters at the end of your current record.
Only other thing I can think of not already mentioned to look at is if the dts package was setup to allow for errors. I’ve done this if my record set has several hundred thousand rows and I know the source data provider sometimes messes up and I just want to skip the bad data. I log the source rows not imported so I can follow up with them. Can you give more information about the type of copy, transformation etc that you’re doing and any logging you have with it.

---

I’m seeing the same problem. It is a known issue, but it was supposed to be fixed in SP2. Here’s the whitepaper:http://support.microsoft.com/kb/300195/en-us However, we’ve got SP3 installed and I’m still seeing the issue. I pasted the log below. This particular run it failed to xfer the last <1000 rows of a very large table and about 60 rows of a table with just 550 or so rows, though it reported successful completion. Anyone have any ideas what might be going on? Thanks. —————————————————–
The execution of the following DTS Package succeeded: Package Name: copy tables to reporting server
Package Description: (null)
Package ID: {CE8B5F25-A107-459C-A4FB-C2C7D67D1EA4}
Package Version: {D8D9EC85-B157-47B2-BDC5-F0514747B2C2}
Package Execution Lineage: {8B14F00D-8AFE-44E4-8777-FB2D59EEBD0C}
Executed On: MyServer
Executed By: me
Execution Started: 1/18/2007 7:55:47 AM
Execution Completed: 1/18/2007 8:15:13 AM
Total Execution Time: 1165.297 seconds Package Steps execution information:
Step ‘DTSStep_DTSTransferObjectsTask_1’ succeeded
Step Execution Started: 1/18/2007 7:55:47 AM
Step Execution Completed: 1/18/2007 8:15:13 AM
Total Step Execution Time: 1165.281 seconds
Progress count in Step: 0
****************************************************************************************************

---

]]>