Exporting results from DTS to two different worksheet of same excel

Question

Exporting results from DTS to two different worksheet of same excel

Ramesh Lende

Ten Centuries

Points: 1034
More actions
February 8, 2006 at 3:19 pm

#166845

Hi
I have 10 different database in SQL Server. All datbases have a common 'USER table". I want to export this user table to excel
using DTS. Problem is, user want that each database result should go into separate worksheet of same excel file. Is it possible to
run some loop in DTS and export results into same excel with different worksheets created on the fly?
Thanks,
Ramesh
February 13, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

I can't say that I have done this, but since nobody else has responded... It should be possible to do this using a variable to name the worksheets. I know that the Excel provider does include the ability to specify worksheet names.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Ramesh Lende Ten Centuries Points: 1034 More actions · Answer 2

I tried this using various techniques, but export to same excel on different tab was not possible. I just used a loop in my Stored Procedure, moved all data into one temp Table and exported that table into excel with additional column of Database Name and user would filter on that column if she wants to see rows related to any particular database instead of going into different tabs.

Thanks,

Ramesh.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Ramesh,

after you have everything in one woorksheet with the database name in one field you can create a macro in Excel that will create sheets based on distinct database names (I assume) and move records to these sheets. Will take some programin' but should work.

Regards,Yelena Varsha

Ramesh Lende Ten Centuries Points: 1034 More actions · Answer 4

Thanks Yelena,

Even i thought of doing the same, but user was happy with report on one worksheet. Also, i was trying to see, if this is possible in SQL Server itself, instead of coding in VBA.

Thanks,

Ramesh.

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 5

You need to create a reference for each sheet that SQL Server understands.

You do this through Names:

Insert > Name > Define

Ramesh Lende Ten Centuries Points: 1034 More actions · Answer 6

Hi Joseph,

I did not understand exactly what are you asking me to do. Can you explain in detail please? I am sorry.. i am little dumb here.

Thanks,

Ramesh.

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 7

To specify where the data needs to go in the workbook, your package will need some sort of reference to point the data at.

If you open the spreadsheet choose where you want the data to go, define Names for those places, then you can export your data to whatever part of the spreadsheet you wish.

Joseph Mulhall SSCarpal Tunnel Points: 4672 More actions · Answer 8

Here's a helpful explanation:

In Excel, a worksheet or range is the equivalent of a table or view. The lists of available tables in the Excel Source and Destination editors display only existing worksheets (identified by the $ sign appended to the worksheet name, such as Sheet1$) and named ranges (identified by the absence of the $ sign, such as MyRange).