SSIS Dervied Column split data into four columns

Question

Hello
I have a field called path that will have a string value like so;

|734|654|890|999
|734|654|890|998
|734|654|888
|734|654|886
|734|550

I want to split the data up in to 4 columns, if a column is empty then I want the column to be empty.

I have some script's to split it, but it only works for |734|654|888 format;

Here are the scripts;

Column1
SUBSTRING(path,FINDSTRING(path,"|",1) + 1,FINDSTRING(path,"|",2) - FINDSTRING(path,"|",1) - 1)

Column2
SUBSTRING(path,FINDSTRING(path,"|",2) + 1,FINDSTRING(path,"|",3) - FINDSTRING(path,"|",2) - 1)

Column3
SUBSTRING(path,FINDSTRING(path,"|",3) + 1,LEN(path) - FINDSTRING(path,"|",3) + 1)

Column4
""

How do I change these script to handle the variable amount of columns I have?

Thank you for reading

Regards

SQLSearcher99

Answer 1

Hi @Graham Rock ,

You can try the following approach.
SSIS expressions have some very handy function(s) for such scenarios: TOKEN() and TOKENCOUNT()

Official documentation: token-ssis-expression

So your derived columns will have the following expressions, one by one:

• TOKEN(SUBSTRING(path, 2, LEN(path)), "|",1)
• TOKEN(SUBSTRING(path, 2, LEN(path)), "|",2)
• etc.

Answer 2

Hi @Graham Rock ,

You can write script task to split data into multiple columns or using TOKEN expression.

Reference:

split-a-single-column-of-data-with-comma-delimiters-into-multiple-columns-in-ssi

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