Exporting data to Excel from a DTS

[Alex]

Hi all,

I've seen this noted in many posts, but nothing I've checked out gives
me any clue on how to do this.

Basically as my topic says, I have a DTS and I simply need to export
some data from a table in MS SQL 2000 to an Excel spreadsheet. I also
need to automate this process so it can run nightly and each new day a
new spreadsheet will be on a network share for us to pick-up.

Can someone point me to the right direction? This needs to be done
totally through the DTS script, so no ImportExport wizard or anything
manual.

Thanks --

Sam

[Ed Enstrom]

Re: Exporting data to Excel from a DTS

Alex wrote:[color=blue]
> Hi all,
>
> I've seen this noted in many posts, but nothing I've checked out gives
> me any clue on how to do this.
>
> Basically as my topic says, I have a DTS and I simply need to export
> some data from a table in MS SQL 2000 to an Excel spreadsheet. I also
> need to automate this process so it can run nightly and each new day a
> new spreadsheet will be on a network share for us to pick-up.
>
> Can someone point me to the right direction? This needs to be done
> totally through the DTS script, so no ImportExport wizard or anything
> manual.
>
> Thanks --
>
> Sam[/color]

I have dozens of DTS packages that create Excel reports. Here is the method I use:

1. FTP a template workbook from source folder to reports folder.
2. Create worksheet (table) in the report workbook
3. Data pump from SQL Server to the workbook.
4. E-mail the report to recipients

Here's the setup details:
1. Create a source folder on the server. This keeps all my report templates.
2. In the source folder, create a template workbook with just one worksheet.
In my company, this sheet has the company name, the name of the report and
standard boiler-plate text about confidentiality , etc. This is the primary
reason I use this approach, since it does not require that I re-create the
standard title worksheet every time.
3. Copy the template workbook from source folder to reports folder. You need
to do this only for the first time. The file must exist in order to create the
connection. SQLAgentCmdExec will need write access to the reports folder.
4. Create DTS package:
5. Create two connections, one to SQL Server, the other to the workbook in the
reports folder.
6. Task 1 - FTP task to copy template from source to reports, with overwrite
7. Task 2 - Execute SQL Task to CREATE TABLE (worksheet) in the report. I
create the table every time the package runs rather than keeping it in the
template because the report may change over time. When this happens, I just
change the data pump and leave the template alone.
8. Task 3 - Data Transformation task to pump data into the worksheet. The
source would be your SQL statement that selects the data from your five tables.
9. Task 4 - ActiveX task to e-mail the report.

The easiest way to create the CREATE TABLE statement is to set up the data pump
task and click on the Create Table button. Copy the Create `New Table`....
statement to the clipboard and paste it into the Execute SQL task, changing the
table name as appropriate. The table name becomes the worksheet name. If you
edit this satement, be careful not to use single-quote character. The delimiter
in the statement is the left-leaning accent mark (the one at upper-left of
keyboard, on same key as tilde).

HTH,

Ed