Derived Column
"TestValue1,100,12/01/2007" this is the string coming from falt file sourse. i want convert this string into three columns in derived column. but i got error i have writen code like First column is : SUBSTRING(FlatOutputCol,1,FINDSTRING(FlatOutputCol,",",1) - 1) second col is :(DT_I4)(SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",1),FINDSTRING(FlatOutputCol,",",2))) Thrid column is : (DT_DBTIMESTAMP)(SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",2) + 1,LEN(FlatOutputCol))) but i got error in second third column. plz clarify me .
March 25th, 2011 10:32am

Please check my blog for this and try to relate your case. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 10:39am

Hi ETL, I am geting only first column. remaining two columns data not getting same time package execution also sucusess. I have done same thing (DT_I4)SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",1) + 1,FINDSTRING(FlatOutputCol,",",2) - FINDSTRING(FlatOutputCol,",",1) - 1)
March 25th, 2011 11:59am

Hi, In your Flat File Source Connection configure the text qualifier as ". Once you configured Text Qualifier then goahead and use Comma DELIMETER. If you do this, your problem will be solved. Hope this will help!! Regards, Guru
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 12:11pm

Hi Guruprasath. i have done that one. but i am not able to get second and thrid column values
March 25th, 2011 12:28pm

Can you use a data viewer to confirm that the column values? Also, check the column mappings in the destinaiton component. Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 12:31pm

Hi Guruprasath. i have done that one. but i am not able to get second and thrid column values check this for 2nd column... SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",1) +1, FINDSTRING(FlatOutputCol,",",2) - FINDSTRING(FlatOutputCol,",",1)-1) for 3rd column SUBSTRING(FlatOutputCol,FINDSTRING(FlatOutputCol,",",2) +1, LEN(FlatOutputCol) - FINDSTRING(FlatOutputCol,",",2)) let me know your observation.. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
March 25th, 2011 12:34pm

Hi ETL. same code i was written. . same problem.
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 12:39pm

hi, can we change any thing in flat file connection manager.
March 25th, 2011 12:50pm

hi, can we change any thing in flat file connection manager. Please share your sample input data... Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 1:00pm

Hi etl, Value1,100,12/01/2007 Value2,200 Value3,300,12/01/2007 Value4,400,12/01/2007 Value5,500 Value6,600,12/01/2007 Value7,700,12/01/2007 Value8,800 Value9,900,12/01/2007 Value0,1000,12/01/2007
March 25th, 2011 1:05pm

Can you please paste the error message which you are getting? Thanks, Guru
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 1:12pm

i am not getting any error message.package executing succuess fully but not getting second and third columns.
March 25th, 2011 1:47pm

why not you try "Delimited File Source". you can do this in derived column but its better if you use the above one.. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 2:04pm

no etl. i have to create the package with derived column as per doc
March 25th, 2011 2:14pm

Hi etl, Value1,100,12/01/2007 Value2,200 Value3,300,12/01/2007 Value4,400,12/01/2007 Value5,500 Value6,600,12/01/2007 Value7,700,12/01/2007 Value8,800 Value9,900,12/01/2007 Value0,1000,12/01/2007 for getting 2nd value from the column ... its not able to find the next "," and fails. so for such type of files you have to use Script component as a source or as transformation next to your flat file source. for sample; you can check James's blog or Sudeep's Blog Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 2:17pm

try these expression on your Derived column.. please replace the "C" to your actual Column name 1st Column: SUBSTRING(C,1,FINDSTRING(C,",",1) - 1) 2nd Column: FINDSTRING(C,",",2) != 0 ? SUBSTRING(C,FINDSTRING(C,",",1) + 1,FINDSTRING(C,",",2) - FINDSTRING(C,",",1) - 1) : SUBSTRING(C,FINDSTRING(C,",",1) + 1,LEN(C) - FINDSTRING(C,",",2)) 3rd Column: FINDSTRING(C,",",2) == 0 ? "NULL" : SUBSTRING(C,FINDSTRING(C,",",2) + 1,LEN(C) - FINDSTRING(C,",",2)) I have tested above expression and its working perfectly...let me know your observation.. Please check this link for your reference Let us TRY this | My Blog :: http://quest4gen.blogspot.com/
March 25th, 2011 2:36pm

In that case you can have a conditional split to split the records with 2 or 1 coma (,) and add a derived column on each output of the conditional split. Create 2 outputs from conditional split: All3: FINDSTRING(FlatOutputCol,",",2) > 0 Only2: FINDSTRING(FlatOutputCol,",",2) == 0 Add derived columns on both the outputs to create the columns and union all the derived column outputs.Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 2:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics