SSIS Dervied Column split data into four columns

Graham Rock 81 Reputation points
2021-07-07T14:38:09.42+00:00

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,941 Reputation points
    2021-07-07T17:30:32.347+00:00

    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.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. ZoeHui-MSFT 32,506 Reputation points
    2021-07-08T01:25:12.493+00:00

    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

    0 comments No comments