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(); } } } } }

Looks to me you are on the right track. As an aside, it resembles HL7 processing I did long time ago. Just in case follow "The Script Component as a Transformation" http://www.sqlis.com/sqlis/post/The-Script-Component-as-a-Transformation.aspx to make sure you do every bit right.Arthur My Blog

Hi Arthur, Thanks for looking at it. When I try to execute it , it gives me errors. I've been going back and forth on this for days now. I'm learing that trial and error stages. I have the script component set up as Asynchronous. My problem is the scripting. I can't seem to complete it. Do you know of any other material I can reference to? I've been reading and googling and asking for days with no leads. Any help is appreciated. Thanks!

this is a sample of Script Component Transformation Asynchronous: http://www.rad.pasfu.com/index.php?/archives/19-How-to-use-Script-Component-as-Asynchronous-Transformation.html if this link doesn't helped you please provide your input data and whole script and I'll try to check the issue.http://www.rad.pasfu.com

Hi Reza, It's similar but I have this to parse out with multiple delimiters.. 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}]