DTS Text Import Error 1

[batteam]

I am getting an error when trying to import a comma-delimited text file into a SQL Server 2000 table using DTS. The error I get says "Too many columns found in the current row; non-whitespace characters were found after the last defined column's data."

The test (that's test, not text) file I'm importing has 100000 rows. The strange thing is, if I break the file into arbitrarily large chunks (ie groups of 10K or 20K lines) and import in multiple steps, I can read the file in just fine. This wouldn't be such a problem except the size of the real file is 12 million rows. Breaking it down is simply not feasible. An attempt to read the whole file in at once results in this error around row 1,056,000. (The test file is rows 1,000,000 to 1,100,000)

In addition, I've looked at the test file in UltraEdit with hidden characters shown, and everything looks normal. Does anybody have any information about this problem?

 

[markVII]

Interesting one. I bet it has something to do with the read buffer overflow (i.e. internal variable to store mapped section of the open file). DTS is nothing more than VBA running in SQL Server environment; so - at least theoretically - limitation of VB apply to DTS.

As a workaround I would suggest split the file in chunks of 1,000,000 rows (12 files altogether); you could easily do it programmatically.

 

[Calen]

Thanks markVII,

I have a similar task to what batteam described (only I have 14 50 to 100 MB files, with more coming) and have been getting the same error message tyring to import them into a SQL Server database. After breaking each of the flat files into 2 (or more) pieces, I was able to import them successfully.

Calen