SSIS SQL to Flat File Fixed Width - Why so difficult?

Question

SSIS SQL to Flat File Fixed Width - Why so difficult?

Shark Energy

SSCrazy Eights

Points: 8389
More actions
March 17, 2009 at 9:35 am

#201101

This is so much easier in Access, DTS, SQL bla bla.
Anyway I am just trying to output a table as is directly into a fixed width flat file. It produces the file but it doesnt' look neat (doesn't show as row after row even with word wrap off. It doesn't seem to be marking the end of a row therefore if you try and import this file back in on SSIS it cannot read it. Why so? Anyone else had this trouble?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You need to select the option that allows a row-terminator. I don't remember what it's called, but it might be "flat file ragged right" or something like that. Otherwise, it does it old mainframe style, where it just starts the next record where the prior one ends, without a row-terminator in it.

- 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

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 2

Excellent. Works like a charm. I had tried this already but made the mistake of switching the existing Flat File connection from Fixed Width to Ragged Right. For some reason it only works doing it from scratch.

Also I have had issues with old service pack installed on my workstation, so this was through the servers SP2 version of SSIS that finally worked.

Cheers again :hehe:

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 3

VERY annoyed with SSIS. Why does it take so much effort to do something so simple through DTS or Microsoft Access?

Lets take Table A. Table A has a "Filler" field stuck on the end that just puts in 200 blank characters. We need this on the file because the company receiving these files has this format - so I can't change the rules. Using the ragged right column it cuts off the final column! WHY IS THIS? It has an output set as 0 and you cannot change this (greyed out). I tried deriving an "EndOfRecord" column just to take the Filler's place but this doesn't seem to make much difference.

One way round I found was sticking a varchar(1) field on the end of each table. The Flat File though then comes out with the 200 character filler field spread out over 2 lines!

Any help here? I feel like I am having to fiddle and fudge something that should be so straightforward. VERY frustrating.

Thanks

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 4

Aha! It seems notepad has a limitation on the amount of characters you can view per line. I viewed the same text file in Textpad and it looks perfect. Had never come across such a limitation on notepad before!

Case closed.

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 5

Actually I would still like to know If anyone else has stuck a derived blank column on the end of their flat file to prevent cut offs? Or is it just me?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

For what it's worth, I created a simple extract (2 fields, ints) from a single table in to a fixed width text file with limited issues. One of the key things i noticed was the timing/flow in which you added items to the SSIS package. For e.g. add an OLE DB data source, configure the connection, configure the content. Then add a text target/destination, selecting Fixed width with row delimiter (automagically adds the delimiter field you've manually added), *before* configuring, drag the output of the source to the new target. Then configure the target, note that doing it this way, it picks up your input columns and adds the row delimiter (in my case, col2, in a zero based collection).

Doing it out of sequence meant not getting the delimiter and a few other issues.

HTH,

Steve.

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 7

Yes very frustrating. I feel as though the moment you want to change a setting you'd be better off creating the whole data flow from scratch rather than updating it.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 8

Shark Energy (3/17/2009)
Excellent. Works like a charm. I had tried this already but made the mistake of switching the existing Flat File connection from Fixed Width to Ragged Right. For some reason it only works doing it from scratch.
.....

Thanks to GSquared and Shark Energy,

Your post has helped me.

Regards,

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 9

I've created fixed-width flat files and what seems to work the best for my purposes is adding a {CR/LF} at the end of the query statement.

+ CHAR(13) + CHAR(10)

Our mainframes are able to see the file in its proper format using the (CR/LF) method.

I've created files without using the CR/LF method and it "looks good" (fixed width) in notepad/wordpad but the mainframe developers have issues with it on there end so to correct it I added the CR/LF.