February 28, 2002 at 2:24 pm
Does anyone know if you can get past the apparent 8000 column limit when importing a text file through DTS (fixed field; not delimited)? When I set the source file I get a message like "Could not find row delimiter in the first 8k of data. Is the row delimiter valid? Y/N" I click yes and it takes me to the screen where you define the fields, but the file only stretches out to 8000 columns. Any help would be appreciated.
Thanks in advance,
Peter J.
February 28, 2002 at 3:05 pm
March 1, 2002 at 7:25 am
All of the data is not going into a single field. It will be split up into hundreds of fields on import. But the input file is over 20,000 columns wide.
March 1, 2002 at 7:44 am
Haven't got a file that large to test but have you tried setting up a table with a text datatype field and import to it?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 1, 2002 at 11:30 am
March 1, 2002 at 12:22 pm
The problem isn't with too much data for one field. Rather, it looks like the DTS Designer can't properly handle an input text file that doesn't have an end of line character within the first 8K of data on the first line. So instead of seeing a 20,000+ position wide file to specify my columns in, I only see 8000 positions. I think bulk copy/bulk insert will be my last resort on this one. I was hoping to be able to manipulate the data on its way in via VB Script but I guess beggars can't be choosers. 😉
Thanks for your suggestions.
Peter
March 3, 2002 at 12:09 am
You should be using the BII.exe utility for text and image bulk copies.
It is documented in BOL.
March 3, 2002 at 2:00 pm
Ok let me ask this. Are you saying that you have multiple columns of data and the overall width is 20000 bytes which is well over the 8000 byte limit of a row in SQL. If so then can you give me an idea of how you columns work and maybe with a combination of a few tables you can build normalized data and import the fields that works with those SQL tables into them, or are you saying DTS just does not see any data to import beyond 8000 bytes on the row?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 4, 2002 at 7:12 am
Antares, the second problem. DTS doesn't see anything to import after 8k.
NeilJ, I'll look into BII utility.
Thanks.
March 4, 2002 at 7:44 am
Ok I understand now, let us know if the other works. If not you may be able to pull it off with active script in DTS to handle the import, I may have code but I don't have a test file that size.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 4, 2002 at 8:22 am
I've done a file of this size once using ActiveX scripting, ADO, and the File Scripting Object. However, it's basically like writing a mini VB program in DTS.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply