How to generate a fixed width file with different data types in each row in SSIS

Question

I want to find out if there is a better way to present a data the way I need using T-SQL and generating a TEXT file in SSIS from SQL command(Exec stored proc).

So I need to present a data in a text file with fixed width for each column, like col1 - 10 alphacharacters, col2 numeric with zero pads, col3 blanks etc. the Total number of characters in a line cannot exceed and must be 275.

However Each Row is going to have different data and different column requirements. So if

First row: '1', col1 - 22 alphacharacters, col2 numeric with zero pads 10 characters, col3 blanks(fill up to 275)
Second row:'2', col1 - date 6 characters,col2 3 blanks col3 30 alphacharacters, col4 numeric, col 5 blanks(fill up to 275)

What I come up with is to concat the row into 1 big string and then Union ALL the rows.

And in SSIS I do Ragged right without columns headers and the text file is coming up exactly as I want, but I wonder if there is a better way to do that

I Figured how to manipulate data with different functions, so i'll just present a code without them to make everything simple

SELECT CONCAT('1',COL1, COL2, REPLICATE(' ',n...)) as 123
FROM MYTABLE

UNION ALL

SELECT CONCAT('2',COL1,REPLICATE(' ',3),COL2, COL3, REPLICATE (' ',n..)) as 123
FROM MYTABLE2

The Layout of results should look like that in a text file (I just put random data for this example to make visualization better)

1Microsoft SQL Server  0000002017
208202019   John Doe                      00000015
208202019   Jane Doe                      00000109
208202019   Will Smith                    00001996

Ferdipux Ferdipux · Accepted Answer · 2019-08-21T06:05:45

Rephrasing your task - you need to set a custom header for your Flat File, and output sows in Ragged format.
The alternative approach is the following. Create a string variable and fill it with header row data as desired. On the Data Flow task which fills in the Flat File Destination, locate [Flat File Destination].[Header] property and set its expression to the string variable from above. This will create a file with defined header. Then on the data flow itself - create a string computed column where you format your output string, and later - save this column into the Flat File Destination.
This is more SSIS approach, since you do not have to do complex SQL statements.

How to generate a fixed width file with different data types in each row in SSIS

1 Answers