Export to Fixed width text file

Export to Fixed width text file

I am trying to export a table to a fixed lenght text file, there is only flat file option and that does not put LF/CR at the end of row, is there any solution?

I believe you can use bcp tobulk export with a format file to meet your needs. SeeFormat Files for Importing or Exporting Data for a place to start.|||

if you use SQL 2005 export wizard or custom SSIS package try to select Ragged right format for flat file destination:

Format

Indicate whether to use delimited, fixed width, or ragged right formatting.

ValueDescription

Delimited

Columns are separated by a delimiter, specified on theColumns page.

Fixed width

Columns have a fixed width.

Ragged right

Ragged right files are those in which every column has a fixed width, except for the last column, which is delimited by the row delimiter.

|||thanks, that worked. How can I execute SSIS package in a stored procedure or in a batch file in Windows?|||You can run it as SQL Agent Job which has SSIS step and this is probably best way or you can run it as batch process from command prompt or from inside SQL procedure. To generate command you can use dtsrunui.exe program which will help you a lot.

Export to Fixed width text file

Export to Fixed width text file

I am trying to export a table to a fixed lenght text file, there is only flat file option and that does not put LF/CR at the end of row, is there any solution?

Ragged right works for me. You can used a derived column to pad your string with spaces if necessary.|||Can you be more specific about what the problem is? I didn't have any trouble setting up a fixed width flat file destination with a LF/CR row delimiter. Add a flat file destination, open it and click New to create a new connection manager, select "Fixed width with row delimiters", give it a file name, switch to Advanced and select the "Row delimiter column" to change the ColumnDelimiter to {LF}{CR} if desired.
|||Row delimiter is not elabled in my environment. What would the problem be?|||It isn't there, or it is but its disabled? Can't think of why either would be the case.

If the above doesn't work for some reason, you can create the same configuration as follows. Open the flat file connection manager (or create a new one). On the General tab, select Ragged Right as the format. For starters, let's not not check "column names in the first data row". Switch to the Advanced tab and add your columns with appropriate DataTypes and OutputColumnWidths. The ColumnType should automatically be Fixed Width for each column but the last one. The last one will be Delimited. Create a dummy column to be your last one. Make its DataType String, OutputColumnWidth 0, and ColumnDelimited to be whatever you want for the row delimiter (you had previously said you wanted {LF}{CR}).
|||

Part of the confusion here may be that you have to pick the Ragged Right option for the flat file connector, not the Fixed Width option. It seems a little counter-intuitive.

Interestingly, if you create your connection manager as Jay suggested originally (by adding the flat file destination, then choosing the New... button beside the connection manager drop-down, the resulting dialog asks if you want a fixed width with row delimiters, then creates a ragged right as Jay's described above.