Split a Single Column into Multiple Columns in SSIS

Posted on 2014-08-13
Medium Priority
Last Modified: 2016-02-11
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.

Question by:AlfredBaker
  • 2

Expert Comment

by:Irek Piwowarek
ID: 40259088
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.

Author Comment

ID: 40259151
Thanks for the suggestion Irek, In my case i dont have a definitive number of columns. What can be done in this case.

Accepted Solution

Irek Piwowarek earned 2000 total points
ID: 40259300
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.

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question