easiest way is to build the records on SQL and then just output to the flat file as a single column.

One approach I've taken on this is to have a table with following layout (shortened for this example)

sortorder1

sortorder2

rownumber

datarecord

sortorder 1 and 2 are used to sort the records on the output file - mandatory for the type of files you mentioned as header(s) need to be first, then data records, then footer

reason for 2 sortorder records is to group data records - e.g. in many mainframe files you have multiple records that form a single logical record and must be processed together.

population of the data record is just a concatenation of all required data with formatting as required, e.g. dates on particular format, numbers on desired format, with leading/trailing sign, with or without decimal separator, with or without quotes around fields and so on.

a typical select to populate the table would be

select row_number ... as sortorder1

, ... as sortorder2

, dbo.quotestring(dbo.formatstring(accountid,size)) -- where Y means quotes and N means noquotes

+ dbo.quotestring(dbo.formatdate(duedate,'format'))

+ dbo.quotestring(dbo.formatnumber(priceused,7,2,'+','L','N')) -- where 7 means number of integer positions, 2 means number of decimal positions, + means output positive sign, L means sign on left, N means no decimal separator

benefit of this approach is that you can do the data records first, then process those records and calculate both header and trailer rows in one go and still be sure that they will be sorted on the output file correctly.