I have an asp page that enables users to write and run queries without
knowing SQL. After running, the users often want to download the results to
MS Excel.

I have written VB code to create a DTS package. The package has a
connection to my SQL database and an excel file. For now (test) I have
hardcoded the Excel Filename/path. The package contains a DataPumpTask and
a step. I can run the code on my server that executes the package and all
seems correct, except that no file is generated.

Does anyone have any Idea why?

Separately, I saved the DTS package to the SQL Server. When I try to run
it, I receive an error stating that the package cannot find the destination
table (excel file). Since the content of the file is not know until
runtime, how can I cause the package to create and populate the file?

Any help appreciated.

You can use CREATE TABLE in an ExecuteSQL task to create a Table/Named Range
in XL.
If no Excel File exists DTS will not automagically create one ala csv files.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Kerry Carroll" <carrolky (AT) fuse (DOT) net> wrote

How can I retrieve a list of columns/column definitions that I can use in
the create table statement?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You can use CREATE TABLE in an ExecuteSQL task to create a Table/Named Range
in XL.
If no Excel File exists DTS will not automagically create one ala csv files.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Kerry Carroll" <carrolky (AT) fuse (DOT) net> wrote

I thought you said you were building the statement in an ASP app so can you
not grab it from there.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Kerry Carroll" <carrolky (AT) fuse (DOT) net> wrote

I can parse the list of field names from the Select clause (if that's the
only way), but don't I need the field definitions to include in the Create
Table statement?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I thought you said you were building the statement in an ASP app so can you
not grab it from there.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Kerry Carroll" <carrolky (AT) fuse (DOT) net> wrote

again you could test the values and I guess you should know what you are
presenting to the user. If you do not then you could always do a character
field

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Kerry Carroll" <carrolky (AT) fuse (DOT) net> wrote

The create table statement in Excel is a little different as in SQL. To see
the code, use data export task instead of DTS designer. To do this, right
click on the database -> All Tasks -> Export data -> select source db ->
choose MS Excel 97-2000 as destination -> Give a filename for the result
file -> choose "Copy tables and vies from thesrouce db" option -> Highlight
the db and click on transform button -> select "Drop and recreate
destination table" option -> At this time, you can click on "Edit SQL"
button that will show the statement to create an Excel table. If you save
the package, you can also view this in the DTS designer window.

If the Excel file is not there, the drop table step will fail. You can add
an exccute SQL task step called "check file existence" in the beginning with
T-SQL like this:

declare @rtn int
exec @rtn = master.dbo.xp_fileexist '\\servername\c$\DTS_3.xls',
@file_exists = @rtn output
if @rtn = 1
exec master.dbo.xp_cmdshell 'del \\servername\c$\DTS_3.xls '

On the create table step, configure the workflow properties as:

source step: check file existence
presendence: success
destination step: create table step


richard

"Kerry Carroll" <carrolky (AT) fuse (DOT) net> wrote