SSIS Flat File Export - "Fixed Width" vs "Ragged Right"

Back in the late 90s, I worked on a number of projects where we had to generate fixed width flat files to send to external systems -- we would use the SQL Server DTS wizard to generate them, and everything worked fine.

 

I recently needed to generate fixed width flat files from SQL Server 2005, so I ran SSIS and everything looks pretty much the same as DTS -- except when choosing "Fixed Width" as the export option, the entire flat file was generated with no linefeeds/carriage returns in it.

 

It turns out there is another option, "Ragged Right", which is exactly the same as "Fixed Width", except that it gives you the option to insert a linefeed character (or CRLF, etc.) at the end of each line of data.

 

Here's a quick walkthrough for generating fixed width flat files with line breaks from SSIS.  For this example, I'm assuming that we'll be selecting data "as-is" from a staging table that is already set up with the correct column widths and contains data exactly as it should be in the fixed width file.

 

1) In SQL Server Management Studio, right click on the database containing the table or data you want to export, and select "Tasks/Export Data..."

image

 

2) For the data source, you can leave the default settings.

image

 

3) For the target, select "Flat File Destination" as the Destination, pick a file name, and under "Format", select "Ragged right"

image

 

4) Click through the next dialog, which gives you the option to specify a query or just select straight from a table.  We're going to select from a table here.  On the "Configure Flat File Destination" screen, pick the table you want to export, and choose the row delimiter.  The default is "CRLF", which is generally good for windows, but if you are sending the file to be processed on another OS or for a specific app, you may need to choose a different linefeed character or sequence.

image

 

That's it!  Execute the package or save it off, and you should have a fixed width flat file with linefeeds.

31 Comments

  • Gee Thanks Mr. Obvious!

    I never knew what ragged right was until today when I was banging my head trying to figure out how to add {CRLF} to the end of the row on my file, and thatnks to you, I'm no longer a bonehead!

  • I was in the same position and it did the trick the problem I'm having is my header Record is not being written now. Any Idea?

    Thanks,
    Doug

  • Thanks for this Blog it was real helpful.

    Marie.

  • Doug -

    If by "header record" you mean the column names in the first row, make sure you check "Column names in the first data row" on the "Choose Destination" wizard step. You need to re-check it each time you do the export, since it defaults to unchecked.

  • this is a great solution!!!! THANKS SOOOOOO MUCH!

  • Thanks for providing this clarification. Your post saved a lot of time and frustration. "Ragged-right" - what was MS thinking?

  • yeah, what they said and thanks.

  • This works, but you end up with variable length of records if your last field is blank. I had to learn this the hard way after my client who uses a unix system rejected to import this file because of variable length fields.

    Thanks

  • Hi,
    I want to import a fixed width raggged right file into sql server 2005 database through ssis.

    How can I do this.

  • Hi,
    I've a rate field numeric(4,2) with values like 2.35 and 0.46 etc. The text file exported this way is excluding the leading zero if the value is "0.46". It's showing it as ".46". How to fix this as I want the result to show 0.46.

    Thanks

  • Thanks.. this was driving me crazy.

  • In my "old life" in publishing, "ragged right" meant something very different - I never would have guessed it was the answer here. Many thanks, you've saved me much time and pain.

  • Thanks, this was exactly what I was looking for! I had just discovered the same thing, that my flat file export from the database had no CR or LF, it was just all streamed together. By trial and error I determined that the ragged right format gave the expected results. I said to myself What is ragged right? and when I googled SSIS ragged right, this article was the first hit! Thanks Guy!!

  • I like you have done many DTS exports of flat files. This gave me the answer I needed trying to build a flag file with line feeds.

    Thank you.

  • Thanks! Elegantly simple answer

  • I think you need to view Dpat comment. Ragged right will not preserve the last columns width, therefore it's NOT the same as fixed width with row delimiter. I would urge people to use caution when using that option.

    Thanks,

    Roma.

  • I am having the same issue as Dpat pointed out. Is there a work around? I need line feeds and the file to remain fixed. Since my last column isn't being preserved (see Dpat's comment above) the 132 output file becomes 72.

  • I'm sorry to hear that DPat and others are having a problem with the last field being blank not emitting a fixed width file -- I would gather that this is the "ragged" part of ragged right. I'm not sure of any specific solution to this issue.

    There are probably a few things you could try, such as putting a single byte at the end of each row, or using a "char" datatype instead of varchar (to fix the size).

  • Hi, Great Atricle!
    Is there a way to specify a name for the exported flat file that is composed/derived from the exported data such as date and maybe an ID number (for example 20100925_0005932.txt)?
    In the above exampls the first part of the name would be a transaction date and the second part of the name would be a transacton/record number.
    You could email me at hmelwani@comcast.net
    Thanks, Haresh

  • I'm trying to place a page break in an rtf, or doc file. Is there a way to do that?

  • Haresh -- you can save the file with whatever name you want when manually creating it with the steps in this post. If you want to automate it, I don't have the exact code, but it should be possible using SMOs and .NET.

    Mr. Dave -- it sounds like you are wanting to export the data to a paginated format with page breaks in RTF or Word -- you may want to take a look at SQL Server Reporting Services, it will provide you with this functionality out of the box, all you need is the SQL you want to run.

  • What is really sad is that there are two bugs that Microsoft has yet to fix over the past 5-6 years with SSIS.

    1) cant import variable length flat files without crashing and burning SSIS
    2) cant import CSV flat file if delimiter is a comma and the data contains an embedded comma (even if the data is wrapped in quotes). choke.

    Its 2011 Microsoft, lets get on the ball and fix these bugs that any import processor should be able to handle. We should not be writing workarounds like we've had to for 5-6 years now. How shameful for you.

  • Excellent. It saved me.

    Thanks a lot

  • so let say I am exporting 5 columns and I am using SSIS, will the last column would be affected using Ragged Right option.

  • Raj: the data in your last column will not be altered or affected if you use the "Ragged Right" option -- but if you need it to occupy a set number of spaces you may need to pad it with spaces or some other character, to maintain a fixed width.

  • Hi gstarbucks, How do you pad the last column with spaces to maintain a fixed width?

  • I was able to do it by adding a dummy column (I called CRLF) to the end of the file connection and put the column delimiter to {CR}{LF}. I put an output length of 2, but not sure if it's required.

    Be sure to go up to the previous last column and change the InputColumnWidth since it doesn't seem to be an option for the last field in the row.
    Best of luck

  • Like other comments; thanks for explaining "ragged right." I've spoken of "fixed width" files for years and they've always been what SQL Server is calling "ragged right."

  • I having the space issues with Ragged right. It's concatenating my first three and last three columns even when I set the size of the columns. Has anyone experienced this?

  • This website is great. I like it.(www.linkspirit.net)N_X_D_S.

  • OK, So fixed width isn't really fixed width and ragged right isn't really ragged right. That makes perfect sense.

Comments have been disabled for this content.