Hi , I am having a task in SSIS . Source as database and destination as excel. How to transform formula column data as well formula into excel destination. Row values are dynamic will changes upon data. Structure of source table as below : column A B rows: 1 2 4 3 2 7 destination in excel column A B C(A+B) r1: 1 2 3 r2: 4 3 7 r3: 2 7 9 I have calculated the column C with derived column transformation. The data is come in to my destination and is OK. In my output (destination excel sheet) if I change the value in rows in column A or B then the calculated column value C also be change with as per formula(User can change A or B values then C value reflect with calculated result). Is there any possibility to derive this in SSIS. So that I can implement with complex formula calculation as well. regards afzal

SSIS cannot do this. SSIS just dumps plain data into the Excel file. If you want a formula in the Excel file, you need to create it yourself.MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Insted of Excel destination you can try to create the destination using a script task, and set formuales maybe a starter for yu http://everlasting129.weebly.com/1/post/2012/2/-how-to-set-excel-subtotal-formula-for-c-vbnet.htmlAbhinav

I would recommend against using the Script Task, as does Microsoft. Instead, create an Excel file with the formulas in it already, and just dump raw data into the existing file. Talk to me now on

I would recommend against using the Script Task, as does Microsoft. Instead, create an Excel file with the formulas in it already, and just dump raw data into the existing file. And with that Todd means create the formula C = A in Excel + B, dump the data in column A and B with SSIS and let Excel take care of column C itself. MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

I would recommend against using the Script Task, as does Microsoft. Instead, create an Excel file with the formulas in it already, and just dump raw data into the existing file. Talk to me now on

Hi Verbeeck, I have resolved the above issue with below query, select * from (select row_number() over (order by a)+1 as ID,a,b from test_sum)as a the output as below : a b ID i have the derived column ----> "= concatenate(a,ID)+concatenate(b,ID) " and exported the data into excel and coming as i expected. But the problem is the o/p is as a char format (left align in excel cell). I have to open the destination file and modify the column data type into general/ numbers. regards afzal.

Hi Verbeeck, I have resolved the above issue with below query, select * from (select row_number() over (order by a)+1 as ID,a,b from test_sum)as a the output as below : a b ID i have the derived column ----> "=A" + (DT_STR,50,1252)ID + "+B" + (DT_STR,50,1252)ID and exported the data into excel and coming as i expected. But the problem is the o/p is as a char format (left align in excel cell). I have to open the destination file and modify the column data type into general/ numbers. note: A,B are cell address in excel destination. regards afzal.

afzal, what is the destination type you choose in drive column? I am trying "=B1+A1" but it just paste same as it is.