SSIS How to split a single string into multiple columns using Derived Column

Question

SSIS How to split a single string into multiple columns using Derived Column

sabarishbabu

Old Hand

Points: 371
More actions
June 3, 2020 at 12:09 am
Go to Answer
#3759649

Hello,
In my source Column having comma separated with text qualifier(“), I would need derived column from source column like below. Please suggest the best SSIS Expression to archive this .
Note : I am trying to derived 50+ Columns and using the string TOKEN(REPLACE(SourceRecord,"\"",""),",",1), it wont work if the Column value has comma.
Thanks
Phil Parkin

SSC Guru

Points: 246960
More actions
June 3, 2020 at 1:09 am
Answer
#3759691
This can be done, if you think laterally: make " your delimiter and your formulas become:
```
TOKEN( SourceRecord,"\"",1)
TOKEN( SourceRecord,"\"",3)
TOKEN( SourceRecord,"\"",5)
TOKEN( SourceRecord,"\"",7)
TOKEN( SourceRecord,"\"",9)
```
Having said that, Frederico's suggestion is better, if applicable.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

if your source is a file then just split it on the file definition by setting up the file with comma delimited as column separator, quoted

sabarishbabu Old Hand Points: 371 More actions · Answer 2

Thanks you Phill !!

Your solution is working as expected, however any of the column value has empty, I did not get the proper value in corresponding output columns.

Source Column/ OutPut Coulumn:

Annotation4

This reply was modified 4 years, 5 months ago by sabarishbabu.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

My solution depends on there being the same number of quotation marks (") in every string which is split.

If an 'empty' column appears as "", the solution should still work. But if the quotation marks are not there, it will not.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sabarishbabu Old Hand Points: 371 More actions · Answer 4

Thanks for your quick reply !! Yes the quotation marks are there in the empty field too, even its not working for if any empty column value.

Source Data

"A","","","","A,B,C,D"

"X","X1,X1","20","+","X,B,C,D"

"Y","Y1,B1","100","+","Y,B,C,D"

"Z","A1","","","Z"

This reply was modified 4 years, 5 months ago by sabarishbabu.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Well, that's unexpected ... but I have a solution for you. If you first REPLACE all instances of "" with " " (a single space) before using TOKEN, things seem to work (though you will have to decide what to do with the space ... set it back to NULL, perhaps).

TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",1) 
TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",3) 
TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",5) 
TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",7) 
TOKEN(replace(SourceRecord, "\"\"", "\" \""),"\"",9)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sabarishbabu Old Hand Points: 371 More actions · Answer 6

Thank you Phill !! Its working as expected I just added trim function 🙂

TRIM(TOKEN(REPLACE(SourceRecord,"\"\"","\" \""),"\"",1))}