How to correctly create a Flat File on export?

Question

How to correctly create a Flat File on export?

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
January 21, 2009 at 9:12 am

#128158

I have a data flow task with an OLEDB source and flat file destination. It seems no matter how I configure the flat file connection I can't get the five rows returned from the data source to yield five records in the flat file.
The flat file needs to be fixed-width records. I've tried "Fixed Width" but there's no way to tell it what the row delimiter is and I get one big long record.
I tried "Ragged Right" but when I went to the Columns tab even though {CR}{LF} is specified as the row delimiter and the input data source is sending over 5 rows the column mapping shows all the data from all five rows and no way to tell SSIS "here's the end of the row".
Am I missing something simple? It seems like this shouldn't be that difficult...

Viewing 6 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

After you redefined it as ragged right, did you refresh the columns?

- 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

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

I deleted the invalidly formatted flat file. I deleted the flat file connection. I added a new flat file connection defined as ragged right. I was unable to set the columns in the "Columns" window, but was able to define the columns in the "Advanced" window. However, I was unable to set the input length for the last column and was forced to add an additional column with 0 length. Now I get a validly formatted file, but this seems awful kludgy.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

Just as a suggestion to get away from kludge code, take a look at BCP with a format file... it'll do just what you want.

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

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

I got it working with Ragged Right. Since there are no files in the target directory I have to set the columns up individually in Advanced, but it works.

I don't know what BCP would buy me here. I'm running 16 separate data flow tasks with 16 different queries creating 16 different files (not my idea, customer requirement). Once I got one working it wasn't bad to copy/paste the connections, etc.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

rschaeferhig (1/21/2009)
I got it working with Ragged Right. Since there are no files in the target directory I have to set the columns up individually in Advanced, but it works.
I don't know what BCP would buy me here. I'm running 16 separate data flow tasks with 16 different queries creating 16 different files (not my idea, customer requirement). Once I got one working it wasn't bad to copy/paste the connections, etc.

Probably nothing except to avoid the kludge code you mentioned. 😉

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