Supposed embedded text qualifiers

Question

Supposed embedded text qualifiers

kolb.branden

Mr or Mrs. 500

Points: 512
More actions
March 2, 2011 at 1:31 pm

#235945

I have a text file that uses | (vertical bar) as the field delimiter and " (double quote) as the text qualifier. When I try to set up a flat file connection manager, it tells me the following:
[font="Courier New"]The preview sample contains embedded text qualifiers ("). The flat file parser does not support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at run time.[/font]
I don't believe there are embedded text qualifiers. I know there are some null values for many fields, which would mean they are shown in the file as "" (2 double quotes). Could this be causing this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

You have a nice file with escaped text delimiters but SSIS still does not care 🙂 This in my opinion is a *glaring* deficiency in SSIS and one that I hit right away when first using it. It seems that they spent all their time on the T and L in the ETL equation and not much on E when it came to flat file sources. SSIS also does not deal with embedded row-delimiters so you a file that looks like this:

"a"|b

"a

a"|b

will be viewed as a three line file even though we want to see field 1 of line 2 as having an embedded line break in it.

If you're proficient in C# check out filehelpers.com

Edit: My assertion and file example in this post is incorrect, please see this post for better examples of how SSIS handles embedded row and column delimiters: http://www.sqlservercentral.com/Forums/FindPost1072653.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

opc.three (3/2/2011)
You have a nice file with escaped text delimiters but SSIS still does not care 🙂 This in my opinion is a *glaring* deficiency in SSIS and one that I hit right away when first using it. It seems that they spent all their time on the T and L in the ETL equation and not much on E when it came to flat file sources. SSIS also does not deal with embedded row-delimiters so you a file that looks like this:
"a"|b
"a
a"|b
will be viewed as a three line file even though we want to see field 1 of line 2 as having an embedded line break in it.
If you're proficient in C# check out filehelpers.com

If the text qualifiers are consitent, that is, if they appear in certain fields in line 1 (the header) then they appear in those same fields in all other lines, then a BCP format file would do the trick including the embedded carriage returns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Related post from same OP: http://www.sqlservercentral.com/Forums/Topic1072204-148-1.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato