Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Another "ARGH": Embedded text qualifiers Expand / Collapse
Author
Message
Posted Monday, August 25, 2008 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
Someone please explain to me why this situation exists. It seems just another bit of MS idiocy to me, but maybe I'm missing something.

A flat file connector has a field for Text Qualifier. So you would think one could actually have text qualifiers in auto-generated flat files, right?

Yet, working on a DTS to SSIS upgrade, I get the following message when previewing my flat file:

"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."

HUH???? What am I supposed to do? Put the qualifiers in after I parse the file????

ARGH!


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #558109
Posted Monday, August 25, 2008 12:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Brandie Tarvin (8/25/2008)
"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."


Ah yes. Reason #1, for Why I Hate DTS/SSIS: It cannot even handle a standard CSV file (CSV has text qualifiers, standard, but NOT on numeric columns/data). Ten years later and it still cannot handle it.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #558343
Posted Monday, August 25, 2008 12:44 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
One of the workarounds is to read an entire line is as a single row, then use the Script Component and write your own logic to break it apart and output.

Vote on it over at Microsoft Conect, maybe they'll fix it; It's still broken in SSIS in SQL 2008.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=312164

Post #558359
Posted Monday, August 25, 2008 1:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Todd Engen (8/25/2008)
One of the workarounds is to read an entire line is as a single row, then use the Script Component and write your own logic to break it apart and output.


Yes, this has been Microsoft's response for most of the last 10 years on this: either pre-process it or post-process it. Both of which call into question the value of an automated import processor in the first place. After all, this is the most common export/import format ever and if they cannot process it without additional coding, why bother with this automated tool in the first place?

Now I just write my own import routines. It may not run as fast as some SSIS/DTS packages, but it saves me many weeks of time & effort.


Vote on it over at Microsoft Conect, maybe they'll fix it; It's still broken in SSIS in SQL 2008.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=312164


Thanks for the pointer Todd, I will.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #558419
Posted Tuesday, August 26, 2008 4:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
Great. Yet another thing to delay the conversion of our "end of lease" box. Boss is going to love this one.

I voted on the issue and commented. I guess I'd better get to work. I have a lot of fixing to do.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #558713
Posted Wednesday, August 27, 2008 4:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
I think I just found an odd workaround to this problem.

When I edit the FF connection, it says there are no columns defined. If I go into a copy of the old DTS package and look at the Transformations properties, I can get the # of columns. I add that # of columns to the FF Connector (Advanced tab) and set the datatype properties & sizes according to what they actually are. Then I go into the Columns and edit the Column delimiter (mine keeps coming up comma even though my files are tilda "~" delimited) and hit "reset" (or is it "refresh"?). I also make sure the Row delimiter is set to CR LF.

Then I go back in the General tab. I remove the check from "Column names in the first data row", flip to the Columns tab so that all the columns are showing as Column001, etc. Flip back to the General tab, recheck the "Column names in the first data row" and flip back to the Column tab. Suddenly, the error message about the embedded text qualifiers is GONE and all my columns preview just fine.

It's a little odd and I haven't tested it (still have a lot of conversion to go through), but it seems to do the trick.

Has anyone else noticed this? BTW, my text qualifier field is still filled in with the appropriate character during this whole process.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #559490
Posted Thursday, July 26, 2012 9:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 26, 2012 9:04 AM
Points: 2, Visits: 4
Thanks Brandie - this worked for me in Visual Studio 2005 SSIS. I just mucked around with all the settings like you mention and the error went away it it loaded with no problems.
Post #1335921
Posted Friday, July 27, 2012 8:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 6, 2012 2:28 PM
Points: 185, Visits: 1,542
I have been using the bulk insert task as I convert dts to ssis, when working with text files. It is not as simple as the import tool, but it has worked well for me so far.
Post #1336536
Posted Friday, July 27, 2012 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
I'm glad my solution has worked for other people. I haven't had this problem in a long time as we are no longer converting DTS packages. Bulk Insert doesn't always provide the functionality I need, so I tend to stay away from it.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1336546
Posted Friday, July 27, 2012 9:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Not sure what you're running into here. Embedded text qualifiers are things like having quotation marks inside a column value.

"Bob","Jones","123 "Main" St","MyCity"

The quotes are "Main" are potentially problematic.

123,"Bob","Jones","123 Main St","MyCity"

Shouldn't be a problem. I've imported thousands of such files through DTS and SSIS, where numeric columns don't have qualifiers and text columns do. Used to admin a system that imported 50-100 such files per day, never had a problem with it. Even had to deal with inconsistencies like some files had column 1 with text qualifiers, and other files didn't. So long as it was consistent within each file.

Do you have a sample of the problem data?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1336581
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse