Hi Experts, we received excel(.xls) files from our client and need to import into sql server table (Sql server 2008 r2) ...the problems we faced here is.. how to read data from excel file single sheet where we have Manager wise sales information’s... for example the data we received looks like this... Team Manager: TM 1 EmpName Product name Sale Amount Emp1 aaa 102.50 Emp2 bbb 53.26 .. .. .. .. .. .. Emp3 ccc 25.50 TM1 Total 181.26 Team Manager: TM 2 EmpName Product name Sale Amount Emp12 bbb 12.50 .. .. .. … … … Emp56 ppp 71.26 Emp4 aaa 525.50 TM2 Total 609.26 Team Manager: TM 9 EmpName Product name Sale Amount Emp23 ccc 66.00 … … … … … … Emp42 bbb 34.60 Emp13 eee 9.00 TM9 Total 109.60 the above data will be in single sheet.. Team mangers and Employees might vary time to time(Team managers and employee are not constant). any suggestion/solution how to get this done. thanks in advance...

I would suggest you use Conditional split to remove the records starting with "Team Manager" or "TM" or "EmpName"

you have to use script component transformation to fetch this data. OR you can use conditional split after the excel source and check the length for the row and redirect the those rows for your next step. Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

· Take one Sctipt Component task as Transformation type after Excel source, Take your desired column as input parameter in script component, Read this column in script task Eg. yourcolumnname.ToString Then edit scriptcomponent task Select input and outputs then select output0 and add one output columns as you required then ok. Then edit script and give your variable value to this output column Row.youroutputcolumn = yourcolumnname Use this new output column in your mapping or destination column ThanksRamesh Rathod