Code to split delimited strings into seperate columns

  • Hi All,

    I am newbie to SSIS and got one requirement. I have a column with the name of email and it looks like the following

    email

    123@ymail.com; 321@gmail.com

    abc@gmail.com, bdc@gmail.com

    edf@ymail.com: def@cmail.com

    Now I need an putput like

    Email1 Email 2

    123@ymail.com 321@gmail.com

    abc@gmail.com bdc@gmail.com

    edf@ymail.com def@cmail.com

    I have many delimiters with in these emails. I need C# code or .Net code to get rid of these delimiters. Can any body suggest me some links or code.

    Thanks in advance,

    Tarak.

  • Read this article by Jeff Moden and adapt the code to your delimiter

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hi Ron,

    Thanks for quick reply.

  • Alternatively, if you're sure there will only be two emails, you could always use the Script Component of the Data Flow Task. Have it be a Transform type Script Component, and create two output columns, then do a string.Split() function against the email input, feeding the two parts to the two output columns.

  • I written like this based on online links. I don't know how to parse a Column values to a variable. Any help would appreciated.

    using System;

    using System.Data;

    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

    using Microsoft.SqlServer.Dts.Runtime.Wrapper;

    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

    public class ScriptMain : UserComponent

    {

    private int i = 0;

    public override void PreExecute()

    {

    base.PreExecute();

    /*

    Add your code here for preprocessing or remove if not needed

    */

    }

    public override void PostExecute()

    {

    base.PostExecute();

    /*

    Add your code here for postprocessing or remove if not needed

    You can set read/write variables here, for example:

    Variables.MyIntVar = 100

    */

    }

    public override void CreateNewOutputRows(Output0Buffer Row)

    {

    string sp;

    string[] columns;

    string delimstr = ",:;',/|";

    char[] delimiter = delimstr.ToCharArray();

    sp = Row.EmailAddr;

    while (sp!= null)

    {

    columns = sp.Split(delimiter);

    if (i > 0 && sp.Length >= 0)

    {

    Row.Primary_EmailAddr = columns[0];

    Row.Secondary_EmailAddr = columns[1];

    }

    }

    i++;

    }

    }

  • Make sure you specify that your Script Component is of type Transform when given the choice.

    In the Input Columns tab, choose the email from your Flat File Source

    In the Inputs and Outputs tab, select the default Output 0 output, and make sure that the SynchronousInputID value is the same as the ID of the Input. Then, click on the Output Columns folder within the Output 0 output, then choose Add Column. Add two columns, give them whatever names you want.

    Next, go in to your script, and Design Script. Paste the code you have there. Replace "Row.EmailAddr" with whatever you called the input, and "Row.Primary_EmailAddr" and "Row.Secondary_EmailAddr" with whatever you called the outputs. Then you're done!

  • If you have a set number of emails, wouldn't it be easier to do this in a Derived Column Transformation?

    I suspect that the Derived Column Transformation may also be more efficient, but have not tested it to be sure.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I was thinking Derived Column Transformation, but there's no built-in split function, so you'd have to have one very, very long script to go through all the different components. Something using SUBSTRING and FINDSTRING and a whole bunch of ? and : statements.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply