none
Asynchronous script Transformation with Script Component RRS feed

  • Question

  • I've been trying to extract a CSV file that has mutliple information in one of the rows. I'm trying to split that row based on different delimiters and output the information in to other rows. I will have to parse out everything between [] and make a new row for the output. AppointmentNumber will be one column but I will need One row for AppointmentTpye, one row for Scheduled, one row for Confirmation before it moves on to the next appointment number. Below is my code, am I on the right track? Any help appreciated. I'm using C# with regex to find a match pattern and split from there. I know I will have to continue the process until everything is split correctly.

    "Appointments"[{"AppointmentNumber":"32335","AppointmentType":"Nail","Scheduled":true,"Confirmation":true},{"AppointmentNumber":"32336","AppointmentType":"Facial","Scheduled":true,"Confirmation":true}{"AppointmentNumber":"32337","AppointmentType":"Massage","Scheduled":true,"Confirmation":true}]    

    /* Microsoft SQL Server Integration Services Script Component

    * Write scripts using Microsoft Visual C# 2008.

    * ScriptMain is the entry point class of the script.*/

    using

    System;

    using

    System.Data;

    using

    Microsoft.SqlServer.Dts.Pipeline.Wrapper;

    using

    Microsoft.SqlServer.Dts.Runtime.Wrapper;

    using

    System.Text.RegularExpressions;

    [Microsoft.SqlServer.Dts.Pipeline.

    SSISScriptComponentEntryPointAttribute]

     

    public

    class ScriptMain :

    UserComponent

    {

     

    private Regex mAppointmentsRegex = new Regex("[{](*.)[}]");

     

    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 Input0_ProcessInputRow(Input0Buffer Row)

    {

     

    if (!Row.XAppointments_IsNull && Row.XAppointments.Length > 0)

    {

     

    //Parse out the text between [] from row.xAppointments
     

     

    String xAppointmentNumbers = Row.XAppointments.ToString().Trim();

     

    int startpos = xAppointmentsNumbers.IndexOf("[");

     

    int endpos = xAppointmentsNumbers.IndexOf("]");

    xAppointmentsNumbers = xAppointmentsNumbers.Substring(startpos + 1, endpos - startpos - 1);

     

     

     

    //Parse out each order entry using regular Expression
     

     

    Match match = mAppointmentsRegex.Match(xAppointmentsNumbers);

     

     

    if (match.Success)

    {

     

    int count = match.Groups.Count;

     

    for (int i = 0; i < count; i++)

    {

    Output0Buffer.AddRow();

    Output0Buffer.AppointmentsNumbers = match.Groups[i].ToString();

    }

    }

    }

    }

    }

    Monday, May 16, 2011 11:16 PM

All replies