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

Villa 231 Reputation points
2024-05-04T00:46:30.9766667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,948 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,589 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,956 Reputation points
    2024-05-05T02:10:48.5166667+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Villa 231 Reputation points
    2024-05-06T01:03:34.2633333+00:00

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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.