SSIS Export SQL Table Data to Colon Delimiter Text File

This SSIS Integration Services article shows how to export SQL Server table records or data to a colon delimiter text file with an example.

The below screenshot shows the records in the Employee table.

Employee Table

SSIS Export SQL Table Data to Colon Delimiter Text File

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

Data Flow Task

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).

Choose the OLE DB Source Connection

We use the SQL Command to choose a few columns in this example. Next, go to the columns tab to check the columns and click the Ok button.

SQL Command to Pick 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. Here, we have chosen the Delimited as the format option.

SSIS Export Data to Colon Delimiter Text File 5

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

Choose the Colon Delimiter Flat File to Export data to SSIS Sql Server table

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 Column tab, change the Column Delimiter to Colon {:}. Next, use the advanced tab to change the Data type or column names. After you finish, click OK to close the window.

Change the Column Delimiter in SSIS to Export SQL Table Data to Colon Delimiter Text File

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

Check the Mappings

Run the SSIS Export SQL Table Data to Colon Delimiter Text File package.

Run SSIS Export SQL Table Data to Colon Delimiter Text File Package

As you can see, the text file has all the records where a Colon separates each column.

SSIS Export SQL Table Data to Colon Delimiter Text File