Exporting data to a fixed-width flat file

Hi,
There's a lot of information on importing data from text files, but not a lot on exporting data to text files... I've checked but found no info on this.

I'm trying to export data from SQL Server to a fixed-width flat file and wondering if I'm doing it the right way.

I use a view as source (using a OLEDB connection manager) and I can see the data without problem.

I defined a Flat File Destination (using a flat file connection manager). When setting up the flat file connection manager, I am asked for a file... Does this mean one should create manually a template file with the desired output format? So I used a production file as template since we're replacing an existing process.

After having set up everything, I run the SSIS only to see all the data on the same row. There are no CRLF...

When I create the file connection manager, there's no way to mention the row delimiter. In the properties I see a "Row Delimiter" field and when I try with "{CR}{LF}" it makes no difference. Interesting to note that, contrary to the HeaderRowDelimiter field, the RowDelimiter field has no drop-down control to give choices.

So I had to return the CRLF as the last field of the source view (SELECT .... ,'CRLF' = CHAR(13) + CHAR(10) FROM ...) to make it work.

Seems odd... Is it the way to go?

Thanks

SSIS implements a row delimiter in an odd way - it parses it from the last field delimiter.

You could Open the Flat File Connection manager - Click on "Advanced" - scroll down to last column and - set ColumnDelimiter to "{CR}{LF}"|||

Hi TVM,

The ColumnDelimiter field is greyed out (unavailable)...

Thanks

|||I tried it out - it seems like the solution you are looking for is to use "ragged-right" format

In that case you can specify widths for columns and last column can be delimited with CR LF|||

Thanks I'll try that!!

EDIT: It works! Thank you very much

|||

fleo wrote:

Thanks I'll try that!!

EDIT: It works! Thank you very much

Please mark this thread as answered.|||

coming in later with a similar issue...

i had probs with a fixed width export, as well -- and the probs didn't seem "rational"; i just couldn't get the fixed-width SSIS solution to work. as soon as i setup up the ragged right solution, everything fell into place.

in my mind, fixed-width = ragged right.

thx/spirits,

seth j hersh

sql