Hi, I have a field, were data in the field are like: aaa\bbb\ccc aaa\bbb\ccc\ddd\eee aaa\bbb\ccc\ddd\eee\ffff\ggg aaa\bbb\ccc\ddd\eee\ffff\ggg\hhh\iiii ...... how would I split this into different field. After every '\', that will be a data for other field. which would be the best way to do this? can anyone let me know. Thanks

where these rows comes from? are they comes from a flat file? if yes, you just need to set a data flow task, and flat file source, and in the flat file connection manager, set column delimiter as "\" if these values comes from a db, then you can use derived column transformation or script component transformation to split them. my suggestion is to use script component because you can split these values with few lines of codes, instead of writing many expressions in the derived column. you can add as many column as you want to script component output columns, and in the script use Row.InputColumn.Split(Convert.ToChar("\")) to split down the values and use a string array to fill data into columns one by one. let me know if you need more details here.http://www.rad.pasfu.com

Thanks Reza, The data Im getting is from DB, and the string are not in order as well, I have the data something like this, aaa\bbb\ccc aaa\bbb\ddd\eee aaa\bbb\ccc\eee\ffff\ggg aaa\bbb\ccc\ddd\eee\ffff\hhh\iiii OutPut shuld be : Col1 col2 col3 col4 col5 col6 col6 col7 col8 aaa bbb ccc aaa bbb NULL ddd eee aaa bbb ccc NUL eee fff ggg aaa bbb ccc ddd eee fff NULL hhh iii if you closely absorve the above data. THE STRING IS NOT IN ORDER.

Reza'a advice stands - use a Derived Column or Script to split the column into its parts. One very salient question though - despite your claim that "the string is not in order", it certainly seems to be. Not all columns are specified though - so how do you tell what column each part should go into? Talk to me now on

Hi Todd, if the string is not in order no worries, I just have to split this column, I dont know how to write the script,can you please let me know. Thanks,

Your script would incorporate code similar to what is used in here http://www.dotnetperls.com/split-vbnet to split a file name path placed into a Input0_ProcessInputRow function. The closest example I could fine to help you is http://www.sqlis.com/post/The-Script-Component-as-a-Transformation.aspxArthur My Blog

first I suppose the number of delimited values are static. add a script component as transformation after the source select the column in input columns and add as many as columns you want under output columns. then select language as vb.net , and edit script, then write this script in the Input0_ProcessInputRow method: Dim columnValueArray As String() = Row.InputColumn.Split(Convert.ToChar("\")) Row.OutputColumn1 = columnValueArray(0) Row.OutputColumn2 = columnValueArray(1) Row.OutputColumn3 = columnValueArray(2) Row.OutputColumn4 = columnValueArray(3) Row.OutputColumn5 = columnValueArray(4) Row.OutputColumn6 = columnValueArray(5) Note that you should use your input column name instead of InputColumn , and your output columns instead of OutputColumn1 to other indexes in the above code. http://www.rad.pasfu.com

Thanks Reza, The data Im getting is from DB, and the string are not in order as well, I have the data something like this, aaa\bbb\ccc aaa\bbb\ddd\eee aaa\bbb\ccc\eee\ffff\ggg aaa\bbb\ccc\ddd\eee\ffff\hhh\iiii OutPut shuld be : Col1 col2 col3 col4 col5 col6 col6 col7 col8 aaa bbb ccc aaa bbb NULL ddd eee aaa bbb ccc NUL eee fff ggg aaa bbb ccc ddd eee fff NULL hhh iii if you closely absorve the above data. THE STRING IS NOT IN ORDER. kalyan couple of questions : 1. Is the max limit of the columns [8] is static or it can vary as well ? and what do u mean by "Not in order " If the only rule for splitting of the data from source is "split into columns number = 8 (split by \ and is value not found default to some other value or null )" please check this solution : Introduce a derived column Transformation where in add 8 columns C1,C2,... C8 with Expressions for Each as below : [in order from C1-C8] FINDSTRING(DATA,"\\",1) == 0? DATA :SUBSTRING(DATA,1,FINDSTRING(DATA,"\\",1) - 1) FINDSTRING(DATA,"\\",2) == 0? "" :SUBSTRING(DATA,FINDSTRING(DATA,"\\",1) + 1,FINDSTRING(DATA,"\\",2) - FINDSTRING(DATA,"\\",1) - 1) FINDSTRING(DATA,"\\",3) == 0? "" :SUBSTRING(DATA,FINDSTRING(DATA,"\\",2) + 1,FINDSTRING(DATA,"\\",3) - FINDSTRING(DATA,"\\",2) - 1) FINDSTRING(DATA,"\\",4) == 0? "" :SUBSTRING(DATA,FINDSTRING(DATA,"\\",3) + 1,FINDSTRING(DATA,"\\",4) - FINDSTRING(DATA,"\\",3) - 1) FINDSTRING(DATA,"\\",5) == 0? "" :SUBSTRING(DATA,FINDSTRING(DATA,"\\",4) + 1,FINDSTRING(DATA,"\\",5) - FINDSTRING(DATA,"\\",4) - 1) FINDSTRING(DATA,"\\",6) == 0? "" :SUBSTRING(DATA,FINDSTRING(DATA,"\\",5) + 1,FINDSTRING(DATA,"\\",6) - FINDSTRING(DATA,"\\",5) - 1) FINDSTRING(DATA,"\\",7) == 0? "" :SUBSTRING(DATA,FINDSTRING(DATA,"\\",6) + 1,FINDSTRING(DATA,"\\",7) - FINDSTRING(DATA,"\\",6) - 1) FINDSTRING(DATA,"\\",8) == 0? "" :SUBSTRING(DATA,FINDSTRING(DATA,"\\",7) + 1,FINDSTRING(DATA,"\\",8) - FINDSTRING(DATA,"\\",7) - 1) * I have defaulted the value with empty string you can modify accordingly ... * DATA is the source column name which will have values [separated by "\"] Hope that helps ... Kunal

Have a look at my blog for similar scenario.My Blog | Ask Me | Test your SSIS skills

Thanks all, this has been resolved. i have used Cursor for this.