How to handle pipe delimiter file in SSIS?

Sanjeev Kumar B G 0

|Col1| |Col2|Col3|Col|

|101| |A|21|DC|

The values in the file starts with vertical bar(|). The first column's values are moved to 2nd column and 2nd column's values to 3rd when I tried delimiter approach.

How should I deal with it in SSIS to load the data into SQL server?

Olaf Helper 45,096 Reputation points

2024-07-17T16:54:03.4933333+00:00

How should I deal with it in SSIS to load the data into SQL server?

Your source data is trashy, so solution: Get clean source data.

In SSIS you can't handle it with default task.
Yitzhak Khabinsky 25,956 Reputation points

2024-07-17T18:34:10.2533333+00:00

*.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. Or data elements are too long or missing completely, i.e. column positions will vary.

The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, encoding, and enforce data quality.
Erland Sommarskog 112.7K Reputation points MVP

2024-07-17T18:54:43.13+00:00

The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, encoding, and enforce data quality.

Yeah, but I doubt that it is Sanjeev's powers to change the file format.

Since I don't know SSIS, I don't have a solution, but if BCP or BULK INSERT are options, I think this can be done with a format file. (Although some more sample data could disprove that idea if there are further regularities.)

2 answers

Greg Low 1,770 Reputation points Microsoft Regional Director

2024-07-19T01:37:02.74+00:00

Don't listen to all the comments about getting the input fixed first. While that's obviously desirable, in the real world we have to work with what we're given, just as Erland noted.

When you have data like this: |101| |A|21|DC|

And you set | as the delimiter, and there are 6 delimiters, then there are 7 columns output. These are the values:

Column 1: blank string
Column 2: 101
Column 3: blank string
Column 4: A
Column 5: 21
Column 6: DC
Column 7: blank string

And you can just select the required output columns in the Source transformation.

I've created a detailed blog post to show you how to do each step. You'll find it here: https://blog.greglow.com/2024/07/19/ssis-reading-pipe-delimited-text-and-selecting-particular-output-columns/
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
ZoeHui-MSFT 37,441 Reputation points

2024-07-18T03:02:00.6833333+00:00

Hi @Sanjeev Kumar B G,

It is hard to do that via SSIS.

Can you edit the source data in the file first so that it can use vertical bar(|) to delimite?
Please sign in to rate this answer.
Sanjeev Kumar B G 0 Reputation points

2024-07-18T13:08:12.77+00:00

Hi,
I tried editing the source file and it worked. But, the problem is I have 10's of files with the same format. So, I need to solve it using SSIS.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to handle pipe delimiter file in SSIS?

2 answers

Your answer