Article ID: 319698
Article Last Modified on 10/30/2003
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q319698
BUG #: 101730 (SQLBUG_70)
BUG #: 354271 (SHILOH_BUGS)
SYMPTOMS
If you use the Transform Data Task (the data pump engine in Data Transformation Services) to import data to a SQL Server database from a text file that has rows with an invalid format, you may observe the following behavior:
- The Transform Data Task might stop responding (hang).
- CPU utilization might increase to almost 100 percent.
- The only way to cancel the task is to end the Mmc.exe process (if the task was run manually) or to end the Dtsrun.exe process (if the task was scheduled) by using the Windows Task Manager.
RESOLUTION
SQL Server 2000
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
SQL Server 7.0
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack
WORKAROUND
To work around this problem, correct the rows that have the invalid format, and then run the Transform Data task to import the data from the text file.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
SQL Server 2000
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.
SQL Server 7.0
This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.
MORE INFORMATION
After you apply the service pack, if you use the Transform Data Task to import data from the text file that has rows with an invalid format, the Transform Data Task detects the error and causes the import to fail. By default, the batch size and max error count for the Transform Data Task is set to zero (0). A batch size of 0 means that all the rows copied from the source are placed in a single batch before submission to SQL Server. A max error count of 0 means that the task ends when the first error occurs. So, if even a single row has an error, that row causes the whole batch to fail on submission. With the default options set, the error message does not specify the problem row number.
To find the problem row in the text file, follow these steps:
- On the Data Transformation Services (DTS) design sheet, right-click Transform Data Task, and then click Properties.
- Click the Options tab. For Microsoft SQL Server 7.0, the Options tab is named Advanced.
- Under Exception file, in the Name text box, type the path and name of the file in which you want to write the exception records. If the file does not exist at package run-time, the file is created by the task.
- In the Insert batch size text box, type 1. A batch size of 1 means that the data is loaded a single row at a time. Each row that fails is counted as a batch failure, and the value of Max error count is incremented by one. In SQL Server 7.0, Insert batch size is named Insert commit size.
- Click OK, and then run the task.
This time the task fails at the problem row.
- To find the exact error message and the row number, open the exception file in a text editor such as Notepad.
- After you know the row number, open the text file in a text editor, review the problem row, and then make corrections to the row.
REFERENCES
SQL Server Books Online; topic: "Transform Data Task"
Additional query words: DTS flat file hang format text qualifier
Keywords: kbbug kbfix kbsqlserv700presp4fix KB319698
