FIX: Transform Data Task Might Stop Responding (hang) While Importing Data From Text File (319698)

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

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"