This SSIS Integration Services article shows how to export SQL Server table records or data to a pipe or vertical bar delimiter text file with an example.
The below image shows the records in the Employee table.
SSIS Export SQL Table Data to Pipe Delimiter Text File
Drag and drop the SSIS Data Flow Task into the control flow region. For more Data Loading options >> Click Here.
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 existing one, but you can create a new one by clicking the New button (arrow pointed).
Select the Employee table from the list. Next, go to the columns tab to check the columns and click the Ok button.
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 Delimited as the flat file format.
Click the Browse button to create a new file to store the pipe delimiter records.
Next, checkmark the Column Names in the first data row option to copy the headers.
Within the Column tab, change the Column Delimiter to Vertical Bar {|}.
Next, use the advanced section to change the Data type or column names. After you finish, click OK to close the window.
Within the Flat File Destination Editor, go to the Mappings tab to check the input and available destination column mapping.
Run the SSIS Export SQL Table Data to Pipe Delimiter Text File package.
As you can see, the text file has all the records where a Pipe or Vertical Bar separates each column.
