Article ID: 319698 - View products that this article applies to.
This article was previously published under Q319698
BUG #: 101730 (SQLBUG_70)
BUG #: 354271 (SHILOH_BUGS)
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:
SQL Server 2000To 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:
(http://support.microsoft.com/kb/290211/EN-US/ )INF: How to Obtain the Latest SQL Server 2000 Service Pack
SQL Server 7.0To 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:
(http://support.microsoft.com/kb/301511/EN-US/ )INF: How to Obtain the Latest SQL Server 7.0 Service Pack
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:
REFERENCESSQL Server Books Online; topic: "Transform Data Task"