how to consider delimiter(,) as a text in SSIS?

Question

i got the requirement from one of my client.

the requirement is

source is a delimiter flat file and delimiter is comma(,). source data contain the comma within the data.now i want to consider that delimiter also text. here is the example source file ex:

col1,col2,col3

10,usa,uk,hr

20,ind,aus,fin

30,europe,marketing

now my output should be like this

without using script task i need to achieve this output remaining transformations

thanks in advance

the following screen shots shows the my trials

Answer 1

If you know for a fact that the 1st and 3rd fields always have a single value, and everything in between belongs to the 2nd field, you can use a regex. Any etl tool should allow you to parse a csv file via regexes.

The following

([^,]*),(.*),([^,]*)

works on the 4 examples you mention.

I'm not a regex expert, I'm sure you can make it much more solid and elegant.

Answer 2

Use quotes as the text qualifier. i.e.

"20","ind,aus","fin"
"30","europe,"marketing"

The text qualifier can be defined in the flat file connection manager. I think else is just guess work and you will code be writing break fix code to handle new scenarios from now until doomsday.