November 21, 2014 at 6:41 am
Hi folks,
I've got a query (most likely my interpretation) of importing ragged right files in SSIS 21012. Take this sample data for example :
01COLUMNACOLUMNBCOLUMNCCOLUMNDCOLUMNE
02COLUMNACOLUMNBCOLUMNC
03COLUMNACOLUMNB
04COLUMNACOLUMNBCOLUMNCCOLUMND
05COLUMNA
A 'full' row would be made of 6 columns:
01COLUMNACOLUMNBCOLUMNCCOLUMNDCOLUMNE
...and imported thus:
Col0 : 01
Col1 : COLUMNA
Col2 : COLUMNB
Col3 : COLUMNC
Col4 : COLUMND
Col5 : COLUMNE
....so far so good.
However one or more of the columns *may* be missing and this is where it all goes pear shaped. I would have hoped that SSIS would come across the Row Delimiter and at that point start a new row, regardless of how many columns *should* be imported. Therefore row 2 in my sample above would be imported thus:
Col0: 02
Col1: COLUMNA
Col2: COLUMNB
Col3: COLUMNC
Col4: NULL
Col5: NULL
When I'm creating the column markers in the SSIS FF connection manager this is exactly what it looks like it should be doing - the data is represented perfectly, however when viewing the preview and running the import it is not, taking the example above:
Col0 to 3 are fine however Col4 is imported as 03COL and Col5 is imported as UMNACOLUMNBCOLUMNCCOLUMNDCOLUMNE
CR/LF is my row delimiter and I've ensured the ALwaysCheckForRowDelimiters is True however it makes no odds.
The reality of this is I have a file with 300+ such columns (the last 7 or 8 of which may not be present) I'm trying to import and it would be useful if SSIS could start a new row upon encountering a CR/LF rather than me having to resort to an alternate approach
All advice greatly received 🙂
November 21, 2014 at 9:04 am
Quick response - I would go with a scripting approach like this[/url]. Does this suck? Yes.
Longer response - the definition of ragged right is that all columns are fixed width except the last column. This is *not* the format you have, You have a fixed width file of differing widths.
Some other choices:
- add a delimiter to your file (e.g. pipe or comma), remembering to have delimiters even when the field is empty.
- add spaces to your file (leaving it fixed width) so that every column exists, even if it's "empty" (all spaces). You'd then have to manage that data (the spaces piece).
Steve.
November 21, 2014 at 10:17 am
You have a fixed width file of differing widths.
Oxymoron, anyone?
November 21, 2014 at 10:34 am
Hey Phil, whadidya call me?!?! LOL
I know, it seems strange but i couldnt think of how else to describe it.
Maybe a better way is - ragged right only applies to the last column width, not the row width.
Steve.
November 21, 2014 at 10:44 am
stevefromOZ (11/21/2014)
Hey Phil, whadidya call me?!?! LOLI know, it seems strange but i couldnt think of how else to describe it.
Maybe a better way is - ragged right only applies to the last column width, not the row width.
Steve, I knew exactly what you meant. But the phrase just leapt out at me and, as it's Friday, I decided some irreverence was in order 😛
November 21, 2014 at 11:42 am
stevefromOZ (11/21/2014)
Quick response - I would go with a scripting approach like this[/url]. Does this suck? Yes.Longer response - the definition of ragged right is that all columns are fixed width except the last column. This is *not* the format you have, You have a fixed width file of differing widths.
Some other choices:
- add a delimiter to your file (e.g. pipe or comma), remembering to have delimiters even when the field is empty.
- add spaces to your file (leaving it fixed width) so that every column exists, even if it's "empty" (all spaces). You'd then have to manage that data (the spaces piece).
Cheers for the quick reply, you're right - my interpretation of ragged right isn't right (jeez, there's too many 'rights' in this sentence already!)
I like the idea of tacking on a number of spaces so every row is the same length.... nice one 🙂
Thanks again!
November 21, 2014 at 12:06 pm
Funnily enough, that works (adding the spaces) but does mean you'll need to clean them up before loading to your target.
Steve.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply