March 2, 2011 at 2:15 pm
I am trying to use SSIS 2005 to import a flat file into my SQL table. The flat file uses | (vertical bar) for a column delimeter and " (double quote) for the text qualifier. Several columns have what I believe are carriage returns or line feeds in them and I need to keep these for the formatting they provide. However, it appears SSIS tries to interpret them, thus causing problems ("Text was truncated or one or more characters had no match in the target code page").
Can what I want to do be done using SSIS? If so, can someone tell me how?
March 2, 2011 at 3:21 pm
Again? The answer is no 😎
http://www.sqlservercentral.com/Forums/Topic1072170-148-1.aspx#bm1072206
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 3, 2011 at 6:28 am
As far as I am concerned I was asking two different (though closely related) questions. Thank you so very much for your help :unsure:
March 3, 2011 at 9:41 am
My fault, I missed the distinction you made. SSIS does not handle embedded column delimiters or embedded text qualifiers as robustly as I would like. SSIS will however handle embedded line breaks provided there are no embedded row-delimiters within the field (that's where I made the mistake pointing you back to my other post, sorry).
For example this will be seen as 3 rows when text-qualifiers are quotes and column-delimiters are pipes:
a|b
aa|b
"a
a
a"|b
This will also be seen as 3 rows when text-qualifiers are quotes and column-delimiters are pipes however you will end up with two pipes in column 1 of row 3:
a|b
aa|b
"a
a||
a"|b
This will be seen as 4 rows when text-qualifiers are off and column-delimiters are pipes because it will not respect the escaped pipe in column 1 of row 3 so SSIS will parse everything after the pipe into a new field causing SSIS to interpret the last 3 bytes ( a|b ) as a fourth row in the file:
a|b
aa|b
a
a||
a|b
This will be seen as 3 rows when text-qualifiers are off and column-delimiters are pipes because there is no embedded column-delimiter in column 1 of row 3:
a|b
aa|b
a
a
a|b
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply