I am trying to create a DTS package. This package will load data from a comma seperated ascii file. one of the fields in this file is long text field with multiple lines of data. The string/text identifier is "(double quote sign). So the example data would be something like,

"this is sample dat a which is split into multiple lines and very long, may be more than 2000 characters long so it goes and goes and ...."

My problem is when sql server runs the package, it truncates the data length to somewhere around 250 characters. I am not sure about the length as there are newline etc characters are invloved. How do get DTS to insert all 2000 characters instead of truncating it...? I appreciate your responses.

Nilesh

-- Anonymous, March 08, 2001

Answers

Ooops, that didnot go well, The example string is actually full of linefeeds, e.g. "dgdfgdfgdfg

dfgdfgd

dfgd dfg dfg dfg "

-- Anonymous, March 08, 2001

Nilesh,

I got this to work in SQL Server 2000 by creating a DTS package that has a (Microsoft OLE DB Provider for SQL Server) connection and a Bulk Insert Task. For the Bulk Insert Task, I had to specify a format file.

For my test, I read in an int column followed by a (multi line) text column. Note that the database table uses a text datatype rather than a char datatype. The trick is to use \" to escape the double quote character. Thus, my format file looks like shown (I have provided extra blank lines so that the format file is readable on the forum):

7.0

2

1 SQLCHAR 0 4 ",\"" 1 IntColumn

2 SQLCHAR 0 2147483647 "\"\r\n" 2 TextColumn

For your reference, my input file looks like this (again with extra blank lines):

1,"This is

long comma delimeted text"

2,"this is sample dat

a which is split into multiple

lines and very long, may be more than 2000 characters long

so it goes and goes and ...."

3,"This is the penultimate

line of text input."

4," ---------------------------------------

README for"

You may also want to reference Microsoft KnowledgeBase article Q132463 at http://support.microsoft.com/support/kb/articles/Q132/4/63.ASP which describes a slightly different format file where the double quote character is put into a dummy variable.

Hope this helps,

Eric

-- Anonymous, March 19, 2001