Split a Single Column into Multiple Columns in SSIS

Posted on 2014-08-13
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
    LVL 1

    Expert Comment

    by:Irek Piwowarek
    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 (
    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

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

    Accepted Solution

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now