May 11, 2011 at 2:20 pm
Hi,
I am having a sp which will return muliple outputs as
Select 'F1','L1'
Select 'F2','L2'
Select 'F3','L3'
Select 'F4','L4'
Select 'F5','L5'
I need to stored each records into a separate sheet in excel.Here there are 5 outputs are there so i need to store in each output in each sheet the excel.How it can be done in SSIS.
Can we do the same in DTS also? if so how
Any help will be appreciated.
Thanks
Parthi
May 11, 2011 at 3:33 pm
I believe that you will need to write this in the Script Task and use the Excel Object Model to create the worksheets that you need.
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/
May 11, 2011 at 4:06 pm
I find it useful to loosely equate Excel Workbook files as databases and to think of a Workbook's Worksheets (aka Tabs) as tables. Just like with SQL Server you cannot load data into an Excel "database" or "table" that does not exist and SSIS cannot create those on the fly for you.
You'll need to create a new Excel file and then create the Worksheets (a.k.a. Tabs) programmatically, either in a Script Task that uses the Excel Object Model...or another method that works well and avoids diving into learning the Excel Object Model is to create the Excel Workbook manually in Excel, including all the Worksheets you want, and save and deploy the empty Excel file along with your SSIS package. Then during the SSIS execution have a File System Task make a copy of the empty Excel Workbook file and then load your data into the empty copy.
When targeting the Excel Destination you can reference the appropriate Worksheet (i.e. table) per the resultset from your SP.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2011 at 9:21 pm
In addition, if you just wanted to overwrite the file itself, you can specify in a SQL task to DROP TABLE `Sheet1$` and then CREATE TABLE `Sheet1$` and it will blank it out. (doesn't actually delete the worksheet, but SSIS will treat it as such)
Also note that the single quotes in the above aren't single quotes, they're the tick mark (standard key value on your Tilde (~) key). That took me an hour to figure out the first time.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 24, 2011 at 1:58 pm
I just ran across a couple nice blog posts by one of our SSC cohorts on this very topic:
> Creating Excel Templates in #SQLServer #SSIS
> Using #Excel Templates in #SSIS - #SQLServer
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply