Flat File not outputting Row Delimiters

Question

Flat File not outputting Row Delimiters

Evil Kraig F

SSC Guru

Points: 100851
More actions
December 3, 2010 at 12:22 pm

#229834

SSIS 2k5. Simple structure. 2 Connections, one is a source of OLEDB (source data, SQL Database), second is Destination of Fixed Width Flatfile. It's for a vendor mainframe feed.
Now, of strangeness, I can't get the destination fixed width to include row delimiters. It's just one long row.
Need someone to make sure I'm not just having a brain fart. Googling gives me all sorts of options for dealing with a flatfile/fixed width as a source, but not the destination.
I'm included {CR}{LF} in the RowDelimiter property (not just the header row delimiter). It's the only place I'm finding to try to affect this.
Anyone know what I'm missing here?
EDIT: Post edited for clarity, I poorly phrased a few things apparently.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Viewing 13 posts - 1 through 12 (of 12 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

I found *a* solution, but it's a bad one from my perspective. I've created a computed column with "/r/n" in the expression and linked that into the final result set (/r = CR, /n = LF). The definition for my 92 char file is now a 94 char file.

This seems counter-intuitive, but it works, I guess. I'd still like to know what I'm doing wrong, since I expect this isn't the "right" way to do this.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sjimmo SSChampion Points: 11139 More actions · Answer 2

Craig, This is a common issue when dealing with files from Unix/OS2 systems. The end of record mark for those systems is a LF and not CR/LF as a windows/DOS system would expect. The easiest way to convert it is if you are able to FTP the file from the Unix system to your windows system. This process will automatically convert the end of record to char(13).

there are converters out there that you can get to convert your file, or you could use VB Script to change the end of record from a char(10) to char(13).

I found this for you:

If you paste the following text to Notepad and save

it as a .vbs file, you can just drop any file onto it and

have it fixed. (Note that in some cases on XP you

may need to adjust security to run a .vbs file.)

'---------------- start text of vbs file ------------

Dim fso, ts, s, arg, fil, fpath, s1

Set fso = CreateObject("Scripting.FileSystemObject")

arg = WScript.arguments.item(0)

Set ts = fso.OpenTextFile(arg, 1, False)

s = ts.ReadAll

ts.Close

Set ts = Nothing

s1 = Replace(s, vbCrLf, vbCr, 1, -1, 0)

s1 = Replace(s1, vbLf, vbCr, 1, -1, 0)

s1 = Replace(s1, vbCr, vbCrLf, 1, -1, 0)

Set ts = fso.CreateTextFile(arg, True)

ts.Write s1

ts.Close

Set ts = Nothing

Set fso = Nothing

MsgBox "All done", 64, "File fixed"

' -------- end vbs file ---------------

Another variation, if you want to save the edited text

to a different file, replace the line:

Set ts = fso.CreateTextFile(arg, True)

with:

Set ts = fso.CreateTextFile(arg & ".txt", True)

Good luck

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

sjimmo (12/3/2010)
Craig, This is a common issue when dealing with files from Unix/OS2 systems. The end of record mark for those systems is a LF and not CR/LF as a windows/DOS system would expect. The easiest way to convert it is if you are able to FTP the file from the Unix system to your windows system. This process will automatically convert the end of record to char(13).

Thanks sjimmo, that's a handy little script for flat file import correction. Unfortunately, my problem is on flatfile export from SSIS. With no CR/LF in play at all without the additional column I included, it won't be able to correct the problem.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sjimmo SSChampion Points: 11139 More actions · Answer 4

Convert the file before SSIS uses it.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

sjimmo (12/3/2010)
Convert the file before SSIS uses it.

I'm sorry, but apparently I'm not being clear enough here. There is no flatfile before SSIS uses it. SSIS creates it.

I realize 99% of the problems out there are regarding imports. This is an export. Thus why I'm assuming I've done something wrong, and my workaround shouldn't be necessary. This shouldn't be this hard, I just apparently can't find the right property/option.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sjimmo SSChampion Points: 11139 More actions · Answer 6

Understood - now you can convert the extracted file by modifying the script to change the end of record. This has been a issue since files were being creaed on one platform and used on another.

Good luck

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

markshelden Grasshopper Points: 21 More actions · Answer 7

More lousy SSIS stuff. Amazing how they've screwed up some of the simplest items.

Anyway, I found the answer in another thread. You need to use the ragged right method from scratch. You can't change an existing connection.

Ironic that we both had this same problem today. Good luck.

http://www.sqlservercentral.com/Forums/Topic677612-147-1.aspx

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

markshelden (12/3/2010)

More lousy SSIS stuff. Amazing how they've screwed up some of the simplest items.

Anyway, I found the answer in another thread. You need to use the ragged right method from scratch. You can't change an existing connection.

Ironic that we both had this same problem today. Good luck.

http://www.sqlservercentral.com/Forums/Topic677612-147-1.aspx%5B/quote%5D

:blink: Oh for the love of... thank you Mark.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

For those playing along at home, Ragged Right needs a specific setup, you need to have one extra column in the advanced section for the CR/LF. Basically, it creates this column for you. So, if you have a FILLER column at the tail, make sure you also add one more column (I called it EndOfLine) with 0 length.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 10

Craig Farrell (12/3/2010)
SSIS 2k5. Simple structure. 2 Connections, one is a source of OLEDB (source data, SQL Database), second is Destination of Fixed Width Flatfile. It's for a vendor mainframe feed.
Now, of strangeness, I can't get the destination fixed width to include row delimiters. It's just one long row.
Need someone to make sure I'm not just having a brain fart. Googling gives me all sorts of options for dealing with a flatfile/fixed width as a source, but not the destination.
I'm included {CR}{LF} in the RowDelimiter property (not just the header row delimiter). It's the only place I'm finding to try to affect this.
Anyone know what I'm missing here?
EDIT: Post edited for clarity, I poorly phrased a few things apparently.

I haven't read the other posts on this thread but, going by the problem cited above, can't you just make an extra column called EOR and populate the column with {CR}{LF}?

As a side bar, it's things like this that baffle me about SSIS, BODI, and several other GUI based data handlers. They are supposed to make things simpler but never seem to live up to that claim. A nice BCP out with a format file would do this "problem" very nicely and with shockingly fast performance.

--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)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 11

Heh... now that I've read the rest of the posts, it looks like I wasn't too far off. Still, BCP would knock the socks off this "ragged right" problem with a format file.

--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)

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 12

Craig Farrell (12/3/2010)
For those playing along at home, Ragged Right needs a specific setup, you need to have one extra column in the advanced section for the CR/LF. Basically, it creates this column for you. So, if you have a FILLER column at the tail, make sure you also add one more column (I called it EndOfLine) with 0 length.

Really? Odd. I've never had to add an extra column using ragged right.

Then again, I wonder how many of my feeds are actually going to mainframe systems. I should check that out.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.