This SSIS Integration Services article shows how to load data from a pipe or vertical bar delimiter text file to the SQL Server table with an example.
To demonstrate this example, we use the Employee With Vertical Bar Delimiter text file that we generated previously.
SSIS Load Data From Pipe or Vertical Bar Delimiter File to SQL Server
Drag and drop the Data Flow Task into the control flow region.
Double-click to open the SSIS Data Flow Region. Then, drag the SSIS flat file source and double-click on it to open the Editor. Next, click the New button to open the SSIS Flat File Connection Manager Editor window.
Click the Browse button to choose the Pipe or Vertical Bar Delimiter text file.
If the text file’s first row has headers, checkmark the Column Names in the first data row option.
Within the Column tab, change the Column Delimiter to Vertical Bar {|}. For more data loading options, refer to the SSIS Sources and Destinations.
Use the advanced section to change the Data type. Here, the column data type must match the destination table; otherwise, it throws an error. After you finish, click OK to close the window.
Next, drag the SSIS OLE DB Destination and double-click on it to open the Editor. Next, click the New button to configure the SSIS OLE DB Connection Manager. Here, we have chosen the existing one.
Select the Load Data from the Pipe Space File table from the list. If not, click the new button to create a new one.
Next, go to the Mappings tab to check the input and available destination column mapping.
Run the SSIS Load Data From the Pipe or vertical bar Delimiter File to the SQL Server package.
Open the SQL Management Studio to see the result.
Also Read
- SSIS Load Data From Tab Delimiter File to SQL Server
- SSIS Load Flat File Blank spaces as SQL Server Nulls