June 21, 2011 at 3:21 am
Dear Forum members,
I have a different requirement. I am executing few SP's and writing the result in excel destination.
The issue I face here is as the excel is like a dashboard. It has some data in it. So when the SSIS package executes. It writes the data below the existing data.
To simplify, I have around columns A to P in the excel dashboard with 112 rows. Out of which all the columns like A, B, C, D, G, H, I, J, K, L, N, O, P have some data. I need to just fill E, F and M columns. So when the SSIS package executes, it writes the data at the bottom of the page i.e after 112 nd row.
Is there anyway where I can directly populate the E, F and M columns along with the default data values in the excel sheet from Row 1 to 112. Explain in detail.
Thanks in advance.
June 21, 2011 at 8:18 am
update
--your table
set E = [value], --what ever you like
F = [value],--what ever you like
M = [value]--what ever you like
Run this code before you export the results to excel.
This will update all rows to [value] for columns E,F and M
HTH
taybre
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 21, 2011 at 8:30 am
taybre (6/21/2011)
update
--your table
set E = [value], --what ever you like
F = [value],--what ever you like
M = [value]--what ever you like
Run this code before you export the results to excel.
This will update all rows to [value] for columns E,F and M
HTH
taybre
Do I have to do it in the ESQL task ?
June 21, 2011 at 8:40 am
Do I have to do it in the ESQL task ?
In you SSIS package, execute a t-sql task before your data push to excel
OR
execute the code the last SP that is run. This option is the fastest as you dont need to publish the SSIS package again after editing.
HTH 🙂
taybre
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 22, 2011 at 2:17 am
taybre (6/21/2011)
Do I have to do it in the ESQL task ?
In you SSIS package, execute a t-sql task before your data push to excel
OR
execute the code the last SP that is run. This option is the fastest as you dont need to publish the SSIS package again after editing.
HTH 🙂
taybre
I tried as you said but i am not able to update the rows from 2 to 113, the result is getting pasted at the bottom of the excel from D 226, E 226, F 226, M 226 to D 337, E 337, F 337, M 337 respectively. Please look at the attachment excel. Thanks in advance and please suggest me a solution....
June 22, 2011 at 9:17 am
Are you using a single excel template that is used each time you export? I saw this 2(ish) years ago, and it had to do that the template has been saved with empty but used rows. Try creating a new excel file that only has the column names and then export to it.
HTH
taybre 😛
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply