sql server dts: SQL to Excel -- sql server dts

SQL to Excel

Mike Voissem
11/18/2005 8:19:14 AM

sql server dts: Using DTS, we'd like to dump the data from a query to an Excel spreadsheet,
and save it off with a user\date generated filename. We call the DTS from
vb.net, and the problem is, DTS needs to have that spreadsheet present at all
times or it breaks the transformation. What is the best way to do this? We
tried clearing out all the cells in the spreadsheet before running the dts
because we didn't want to append the data, and the dts fails because the
"table" didn't exist. Anyone else run up against this problem?
Mike
--
Mike Voissem
Lead Software Engineer

RE: SQL to Excel

Dave
11/18/2005 9:01:08 AM

Create the table dynamically use the excel connection.

Or you can use asp.
Use this at the top of your asp query page
Response.ContentType = "application/vnd.ms-excel"
The query result will be rendered in excel within the browser and the user
can save the file as needed.

A more sophisticated solution is to implement reporting services.

[quoted text, click to view]

Re: SQL to Excel

Wm. Scott Miller
11/18/2005 12:07:32 PM

Mike:

What you have to do is use Execute SQL Tasks to create your "tables"
(actually sheets) in the excel file. I usually drop the "tables" and
recreate with:

DROP TABLE [NAME]
GO

CREATE TABLE `NAME` (
`NAME_LAST` VarChar (20) ,
`NAME_SUFFIX` VarChar (10) ,
`NAME_FIRST` VarChar (25) ,
`NAME_MIDDLE` VarChar (15) ,
`ID` Long )
GO

Just have your "Existing Connection" set to your Microsoft Excel connection.
You may be able to do a DELETE FROM [NAME] to cleanup. I seem to recall
some caveat to the DELETE, but can't recall it right now. You can have no
file there, and the Microsoft Excel connection should create. If not, try
using an ActiveX script task and the
CreateObject("Scripting.FileSystemObject") to make a copy of your file
(either make a copy of a template you have ready or copy after you export
the data...multitasking being the deciding factor here) to the right
user/date.

Scott

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.