SSIS Export SQL Data to Fixed Width with Row Delimiters File

This SSIS Integration Services article shows how to export SQL Server table data or records to a fixed width with row delimiters text file.

The below screenshot displays the records in the Employee table.

Employee Table

SSIS Export SQL Data to Fixed Width with Row Delimiters Text File

Drag the SSIS Data Flow Task into the control flow region.

Add Data Flow Task for SSIS Export SQL Data to Fixed Width with Row Delimiters File

Double-click to open the Data Flow Region. Then, drag the OLE DB Source and double-click on it to open the Editor. Next, click the New button to configure the OLE DB Connection Manager. Here, we have chosen the current one, but you can create a fresh one by clicking the New button (arrow pointed).

Add Ole Db Source to configure the oledb connection manager

We choose the Employee table in this example. Next, go to the columns tab to check the columns and click the Ok button.

choose the Employee table

Drag the flat file destination and connect the OLE DB source to it. Next, Double-click on the flat file destination to open the Editor, and click on the new button to choose the flat file format as Fixed Width with Row Delimiters.

Choose Fixed Width with Row Delimiter option in SSIS to export SQL Server table data to flat file

Click on the Browse button to create a new file to store the records.

create a new file for SSIS Export SQL Data to Fixed Width with Row Delimiters File

Next, checkmark the Column Names in the first data row option to copy the headers.

checkmark the Column Names in the first data row

Within the Advanced tab, change the InputColumnWidth value to accommodate the row items. For this, check the maximum length of each column and add that value to it.

Change the InputColumnWidth value

Go to SQL Management Studio and type the following query. Remember to replace the EmpID with your SQL own columns.

SELECT MAX(LEN(EmpID)) InputColumnWidth

Change the InputColumnWidth value for the remaining columns.

Assign the InputColumnWidth value

If you observe, it has an extra column called the Row delimiter column that is empty. After you finish, click OK to close the window.

extra Row delimiter column added

Within the Flat File Destination Editor, go to the Mappings tab to check the input and available destination column mapping.

Check The Input and Destination Columns Mapping

Run the SSIS Export SQL Data to Fixed Width with Row Delimiters Text File package.

Execute SSIS Export SQL Data to Fixed Width with Row Delimiters Text File package

As you can see, the text file has all the records where a Fixed Width separates each column and adds an extra Row Delimiter column.

Fixed Width with Row Delimiters Text File