Export SQL Data to Fixed Width Text File with Multiple Record Types

0
votes

I need to create an export of data from SQL Server (multiple tables) into a fixed width text file. The text file will have rows that are different based on the record type.

  1. Header Info (Customer, Address)
  2. Line Item Info (Customer, Item, Qty)
  3. Summary Info (Customer, Total Qty)

Any suggestions to accomplish this efficiently?

I'm currently re-casting all columns into char to create the "fixed width" then using SSIS to merge the tables before exporting as a ragged right text file. However, because not all the widths are the same, I'm having to concatenate the line item info into one column to make the merge work. Also, the header info is being merged AFTER the line item info, not before so there's a sorting problem there. Not sure if I'm going down the right path?

Hope that made sense... this export is used to import into a COBOL type system.

Thanks,

2
sql-servertsqlssis

2 Answers

0
votes

Using SSIS create three data flow tasks, each for creating a single text file with the fixed-width format.

File 1: Header Info

File 2: Line Item Info

File 3: Summary Info

Then concatenate them together into a fourth file using the approach described in the following link:

How to concatenate 2 files in SSIS (Integration Services)?

Hope this helps.

0
votes

For these sorts of problems, I reach for SSIS. It eats this kind of thing for lunch