In SSIS, what is the best way to take a column with comma separated strings and separate them to

Hi There,

Can anybody suggest me what is the best way to take a column with comma separated stings and output them into multiple columns with strings?

for example if I have a column with "watertown, newton" as a string, I need to separate them to two columns with watertown and newton values?

Is Derived column transformation the best way to do it?

Thanks.

Sam.

From what I've seen your 2 choices are using a derived column or writing a script. For your case the derived column seems best - just use a combination of Substring and findstring.|||Yeah that's what I thought. Thanks for the quick response.|||

Chris,

While I am doing this, I need to look up for an id based on the city name and add as a new column. So in one row of data if I have four cities, I need to have 4 columns of their corresponding ids. I am using the look up transformation to get the id, but do I need to have 4 look up transformations to do that or can it be done by 1 look up transformation?

Thanks.

Sam.

|||Unpivot ur data, do the lookup and get the id using a single loopkup transformation, pivot it back.