How to handle text file with delimiter as ~|~ in SSIS Package

Question

I received text file from external vendor with appears to be Pipe Delimited but it has very unusual character before and after Pipe (|).

I am using SSIS Package (VS 2019) to import the data into SQL Server database 2019. I am using SQL Agent Job to execute the SSIS Package on a daily basis.

The unusual character before and after pipe (|) is ~.

The sample text file from external vendor with data looks something like below:

pscuClientId~|~id~|~instUserId~|~username~|~

ADDITIONFI~|~6bfc76c3-d9e4-4d5b-bb60-6f9d2296f107~|~~|~nschmidt@lumindigital.com~|~

How can I identify this kind of delimiter in SSIS Package?

Thank you in advance for your help.

Answer 1

Hi @Villa,

You need to specify in the "Flat File Connection Manager Editor", the following two settings:

General Tab => Text Qualifier: ~ (tilde) character.

Columns Tab => Column delimiter: Vertical Bar (|)

For the reference: https://www.youtube.com/watch?v=26Fim4NGmLg

Answer 2

Thank you very much for your help. It has solved my problem with your suggestion.