Hi, I have a excel file(.xls) like these: ID Name Group Seq Acno Addition Subtraction 1 ABC A 1 0111 5 0 2 BCD B 2 0222 9 0 6 0 3 EDF B 1 0333 0 2 The Seqno column has values depending upon the value of the sequence number the no.of columns will change I need to create a output (.xls) file like these: ID Name Group Seq Acno Amount 1 ABC A 1 0111 5 2 BCD B 2 0222 9 2 BCD B 2 0222 6 3 EDF B 1 0333 -2 My requirement is depending upon the sequence number no.of rows will be added. Can anyone direct me in achieving the above requirement. Thanks, latha

Hi latha, You basically is adding a raw so a Script Transformation can be used similar to what is described in this article: http://www.bimonkey.com/2009/09/the-script-transformation-part-2-as-a-source/Arthur My Blog

is the number of columns different ? or they are static? for example how many columns you expect to see after 6 0 ?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

The number of columns are not static. Depending upon the Seqno the no.of columns changes. For eg: in above second row has seq no=2 so two extra columns (6 0) added if it is 3 there will be still 2 more rows added(like 6 0 8 0) like that.latha

@arthur: Is there a possibility of using script task than script component.latha

They are similar, but expose different methods, Since you need to add a raw to the buffer you ought to use the Script Transformation.Arthur My Blog

you can do this with script component as source, this is a sample of script component as source: http://www.rad.pasfu.com/index.php?/archives/38-Script-Component-as-Source-SSIS.html in the script component you should manually connect to excel from code and read data from it, this is a sample c# code to read data from excel file: http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspxhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Have a look at the blog I have on splitting single line to multiple lines. I have used an example of flat file you could use Reaz's link to get the execl connectivity.My Blog | Ask Me | Test your SSIS skills

I have excel file as source how can i split one row into parts. Any separator can i use to split it??latha

If you are asking about splitting such a row based on a logical condition then you can use the Conditional Split component: http://sqlserverpedia.com/blog/sql-server-bloggers/conditional-split-transformation-in-ssis-2008-uses-and-examples/ or http://www.bimonkey.com/2009/06/the-conditional-split-transformation/ Arthur My Blog

@Reza: in your code you used Dim columnArray As String() = line.Split(Convert.ToChar("|") to split the line into parts as your source is flat file and delimiter wit "|". is there a way i can achieve it since my source is Excel and not able to figure it out how to split the line . Thanks,latha

@Reza: in your code you used Dim columnArray As String() = line.Split(Convert.ToChar("|") to split the line into parts as your source is flat file and delimiter wit "|". is there a way i can achieve it since my source is Excel and not able to figure it out how to split the line . Thanks, latha did you looked at the second link I suggested? you can get each cell data separately, as that link described, just use that wayhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

@Reza: I have the incoming file as .CSV file so i tried your approach i solved the problem thanks.latha