Splitting input column into dynamic multiple output columns

Question

I have a source flat file wherein one of the column is a text with no fixed pattern except a few keywords and semicolons. I need to extract information from that column based on keywords like DOB, POB, nationality so first I was trying to split the column by delimeter ';'. But found no easy way to do this. I read we could use script component transformation, but even there didnt find how to input this one column and output multiple dynamic columns based on a delimiter!? Most of the solutions show adding more rows to output or adding fixed set of columns. But my requirement is based on input text and no. of delimiters it has (;) the dynamic column outputs should be returned and then I can store this output to a table in database. Please help me achieve this.

Sample data of the column from the flat file source I described above:
-0- ,"DOB 01 Jan 1957 to 31 Jan 1957; nationality aaa; Gender Male; a.k.a. 'asd'; a.k.a. 'dfs'."
-0- ,DOB 1961; alt. DOB 1962; POB bbb; nationality Iksi; Additional Sanctions Information - Subject to Secondary Sanctions; Gender Male.
-0- ,"DOB 30 Dec 1960; POB tmed, Pherds; nationality Pkidtn; PP AC8342eee (OKL) issued 22 Aug 2007 expires 20 Aug 2012; alt. PP Booklet A482123 (PLJS); National ID No. 6100-9111-1 (Temds); alt. National ID No. 121245555 (Bludsed); a.k.a. 'CHCHASKE'."
-0- ,DOB 1955; alt. DOB 1956; citizen Saira.

Answer 1

Hi @Manthan Patel ,

It's a little hard to meet your needs.

I'd suggest that you may transfer the data to sql database table and then use t-sql to deal with your needs.

It's more convenient to split input column into dynamic multiple output columns.

You may also post the issue with the tag of sql-server-transact-sql for tsql help.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 2

no. of delimiters it has (;) the dynamic column outputs should be returned

SSIS and other ETL tools are not really good in handling dynamic columns from source.
The closest I know is a Flat File Source with "Ragged right" file format.