How to create a fixed length file from SQL database and control the output field width

Question

We are trying to create a flat file from OLE DB source. When we try to define the output column width, it goes right back to 255.

Any help is very much appreciated as this is a crucial function we need to make happen.

Thank you!

Answer 1

Hi @Zak Jaeb,

There two methods to achieve that:

1. In the Flat file connection manager - Advanced Tab, select all columns (using Ctrl key) and change the data length property for them all in one edit.
2. Go to your package name, right click on it and select the option View code from the list presented to you. XML code will then come up. Hit Ctrl + F to get a “Find and Replace:” window. On “Find What” type in DTS:MaximumWidth="50" and on “Replace with:” type in DTS:MaximumWidth="500". Make sure that under “Look in” the selection is Current Document.

Check it out here:

• https://stackoverflow.com/questions/44964823/ssis-flat-file-default-length
• https://social.technet.microsoft.com/wiki/contents/articles/23883.ssis-hints-tips-handling-flat-files.aspx

Answer 2

In the Data Flow, Right click on the task "OLE DB Source" and select "Show Advanced Editor...":

Go to the tab "Input and Output Properties", and then expand "Output Columns". Change each column's DataType to string [DTSTR] or Unicode string [DT_WSTR] and set Length to the number you want, i.e., 100:

Click OK.

Open "Flat File Connection Manager". In the General tab, select "Ragged right" from the dropdown list Format:

And then go to the Advanced tab. Change each column's DataType to the same as those in "Output Columns" and each column's InputColumnWidth and OutputColumnWidth to the same length as those you entered in "Output Columns".

You may need to open the task "Flat File Destination" and then click OK if you see an warning on the task. If you run the "Data Flow Task" you should get the flat file with the fixed length you set.

Answer 3

Hi @Zak Jaeb,

By default, the Flat File connection manager sets the length of string columns to 50 characters. In the Flat File Connection Manager Editor dialog box, you can evaluate sample data and automatically resize the length of these columns to prevent truncation of data or excess column width.

To modify the length of output columns, you set the Length property of the output column on the Input and Output Properties tab in the Advanced Editor dialog box.

Check this official documentation: https://learn.microsoft.com/en-us/sql/integration-services/connection-manager/flat-file-connection-manager?view=sql-server-ver16

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.