Export to Excel

Question

Post reply

Export to Excel

Dale Burrell

SSCommitted

Points: 1677
More actions
June 2, 2005 at 9:20 am

#61140

I want to make a DTS package that exports to Excel. Now I can do this using the export wizard in EM, but I need to export 3 complex queries into 3 different work sheets.
Can someone please point me in the right direction to obtain the info on how to do this?
Thanks,

Viewing 4 posts - 1 through 3 (of 3 total)

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

ND-215452 SSCrazy Points: 2017 More actions · Answer 1

In DTS package,

create a database connection and a Excel connection

create data pump between 2 connections.

put your query in the source tab.

I hope this what you after...

ChrisMoix-87856 SSCertifiable Points: 7288 More actions · Answer 2

In your destination tab, it treats the worksheets like tables (as if it were a SQL Server Database destination). It's kind of clunky, but play with it and you can do three transformations each with a different destination 'table' in an Excel workbook.

Brock McDonald SSC Enthusiast Points: 185 More actions · Answer 3

I've worked with this several times, you will need to create your connecting to the Spreadsheet to use when creatingtables (worksheets) and transforming data.

Create an Execute SQL Task, with the CREATE TABLE statements for each Worksheet:

CREATE TABLE `Sheet1` (
`LastName` VarChar (25) ,
`FirstName` VarChar (15) ,
`PurchaseDate` DateTime )
go
CREATE TABLE `Sheet2` (
`LastName` VarChar (25) ,
`FirstName` VarChar (15) ,
`PurchaseDate` DateTime )
go

Then create your Transformations, and populate the spreadsheet.

FYI, if you want to have the spreadsheet formatted (Columns Bolded, Cells Formatted, etc.) then you will need to create the Spreadsheet and format it, then just reuse over and over again OR write VBScript that uses the Excel Object to perform your formatting.