Split a Single Column into Multiple Columns in SSIS

Hi Experts, I have a Source table which has one Column and the values in the Column are Pipe Seperated
Ex: 1234|5678|91012|abc|def|ghij|klm|nop

I have to split this column into multiple columns and then populate it into a target table.

I have to Split the Column Whereever I find a Pipe symbol. How can I do this using SSIS. Any help or suggestion is highly appreciated.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Irek PiwowarekCommented:
Some suggestions assuming that maximum number of new columns is well known.
1. Use Derivates columns component and together with expressions crate always maximum number of columns using substring, findstring or better to use TOKEN (from SQLServer2012) function (http://technet.microsoft.com/en-us/library/hh213216.aspx)
2. Use Script component and split string there using C# or VB and assign to variables which you can assign in derivate columns. With script you can better control any extreme cases like no data, only one column etc.
Hope that helps.
AlfredBakerAuthor Commented:
Thanks for the suggestion Irek, In my case i dont have a definitive number of columns. What can be done in this case.
Irek PiwowarekCommented:
Maybe use some imaginary maximum like 20, 50, 100 columns.
You need anyway to create table with some specific column definition.
Quite complex but still doable would be.
1. Recognize number of columns (number of 'pipe' occurances + 1)
2. Split string into columns using derivates or script
3. Dynamically in sql create table with recognized number of columns named col1 to colN.
4. Output columns
Another idea would be to avoid columns and in a loop (number of 'pipe' occurances + 1) save to table values (ColumnNumber, ColumnValue). So instead a lot of columns you will have a lot of rows. Later you can pivot table although without fixed column number still using dynamic sql.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.