August 25, 2008 at 7:17 am
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!
August 25, 2008 at 12:15 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2008 at 12:44 pm
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
August 25, 2008 at 1:41 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 26, 2008 at 4:44 am
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.
August 27, 2008 at 4:56 am
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.
July 26, 2012 at 9:09 am
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.
July 27, 2012 at 8:10 am
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.
July 27, 2012 at 8:28 am
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.
July 27, 2012 at 9:17 am
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
July 27, 2012 at 10:38 am
Gus, this issue was solved years ago. It was when I was converting from 2000 to 2005. I posted the solution and someone was just posting a thank you note to the thread.
July 27, 2012 at 11:16 am
Brandie Tarvin (7/27/2012)
Gus, this issue was solved years ago. It was when I was converting from 2000 to 2005. I posted the solution and someone was just posting a thank you note to the thread.
That's what I get for not looking at the timestamps.
- 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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply