question

GrahamRock-8444 avatar image
0 Votes"
GrahamRock-8444 asked YitzhakKhabinsky-0887 edited

SSIS Dervied Column split data into four columns

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @GrahamRock-8444,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @GrahamRock-8444,

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.