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

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.

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:

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:

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.

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.

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:

1. On the Data Transformation Services (DTS) design sheet, right-click Transform Data Task, and then click Properties.
2. Click the Options tab. For Microsoft SQL Server 7.0, the Options tab is named Advanced.
3. 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.
4. 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.
5. Click OK, and then run the task.
   
   This time the task fails at the problem row.
6. To find the exact error message and the row number, open the exception file in a text editor such as Notepad.
7. 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"