DTS Export to Excel

I'm looking for the best way to export the results of a parameterized stored procedure (SQL 2000) to excel. I can do this with DTS using global variables for the parameters, but each time I execute the package it appends the data below where the previous data was, leaving a bunch of blank rows. I need the data to always be appended to the 2nd row (replacing the old data) because I have a chart based on a dynamic named range in Excel. Is there an easy way to do this in DTS, or should I approach this another way (ADO, ActiveX Scripts, .NET, etc.)? Thanks,

Dave

Try putting the query directly into the spreadsheet. Use Data -> Import External Data -> Query. You can set up the query to take parameters that you are either prompted for or are taken from a cell. You can also configure the query to always replace the previous data.|||Thanks. I was thinking that using Microsoft Query as you described might be the way to go. I actually came up with something that works using DTS with ActiveX scripts but it's alot "clunkier" than using Microsoft Query from Excel would be.sql

dts : call 2 procs within one execute sql task

Hi :

Can i call 2 procs within one task?
I have sp_proc1 ? (and have declared one global variable as input
parameter)
now i have another sp_proc2 which uses same input parameter

but if i write two statements like this within one task, i get an
error

exec sp_proc1 ?
exec sp_proc2 ?

I can solve the problem by writing them in 2 separate tasks, but would
like one task.

Please help..

thanks
Rashikarashika (rshivaraman@.ibs.com) writes:
> Can i call 2 procs within one task?
> I have sp_proc1 ? (and have declared one global variable as input
> parameter)
> now i have another sp_proc2 which uses same input parameter
> but if i write two statements like this within one task, i get an
> error

Standard question: what error is that? Even if the error appears to
be gibberish to you, it may not do to anyone else. (But since I don't
know DTS, it might be gibberish to me too. :-)

> exec sp_proc1 ?
> exec sp_proc2 ?
> I can solve the problem by writing them in 2 separate tasks, but would
> like one task.

You could always write a wrapping procedure:

CREATE MyWrapper @.s <datatype> AS
EXEC sp_proc1 @.s
EXEC sp_proc2 @.s

Note: the prefix sp_ is reserved for system stored procedure, and SQL Server
first looks for these in the master database. Do not use sp_ for your own
prodedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DTS - With Conditions

Hi All,
I have DTS which uses global variables and dynamic mapping to map to the
variables as per the parameters that i send from the Stored procedure that
used to call / execute the DTS.
My DTS has to export data from a SQL Server (Join Based Query) to a TARGET
.DBF file.
problem 1: How do I create the target .dbf file in the specied folder if
that does not exists? I pass the target folder name and dtf file name from
Stored Procedure and that has to be mapped for the dBase Connection.
problem 2: If the Query (that will get executed before the dbf file will get
create) does not return any rows then I dont want to create the dbf file. My
DTS should stop there by logging some message in log (if this is possible?)
Please suggest.
Thanks
PrabhatHi Prabhat,
Please try to use Import-Export whizard tool available in SQL Server. This
is a whizard to program your DTS aswell.
You can then save the steps as a DTS package
hope this gives a hint on how to go about
thanks and regards
Chandra
"Prabhat" wrote:

> Hi All,
> I have DTS which uses global variables and dynamic mapping to map to the
> variables as per the parameters that i send from the Stored procedure that
> used to call / execute the DTS.
> My DTS has to export data from a SQL Server (Join Based Query) to a TARGET
> ..DBF file.
> problem 1: How do I create the target .dbf file in the specied folder if
> that does not exists? I pass the target folder name and dtf file name from
> Stored Procedure and that has to be mapped for the dBase Connection.
> problem 2: If the Query (that will get executed before the dbf file will g
et
> create) does not return any rows then I dont want to create the dbf file.
My
> DTS should stop there by logging some message in log (if this is possible?
)
> Please suggest.
> Thanks
> Prabhat
>
>|||Hi Chandra,
Thanks for the hint. But I believe that In the Import / Export we can start
a Wizard if the file does not exist.
Thanks
Prabhat
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:31D82797-6A69-4ECF-AE96-3845AC1FA694@.microsoft.com...
> Hi Prabhat,
> Please try to use Import-Export whizard tool available in SQL Server. This
> is a whizard to program your DTS aswell.
> You can then save the steps as a DTS package
> hope this gives a hint on how to go about
> thanks and regards
> Chandra
>
> "Prabhat" wrote:
>
that
TARGET
from
get
file. My
possible?)|||If the file already exists, u can over write the file
"Prabhat" wrote:

> Hi Chandra,
> Thanks for the hint. But I believe that In the Import / Export we can star
t
> a Wizard if the file does not exist.
> Thanks
> Prabhat
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:31D82797-6A69-4ECF-AE96-3845AC1FA694@.microsoft.com...
> that
> TARGET
> from
> get
> file. My
> possible?)
>
>|||That is not a problem. But how Do I start the wizard if the file does not
exists?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B9016AB0-D40E-4EAF-A5A9-66D3326F267A@.microsoft.com...
> If the file already exists, u can over write the file
> "Prabhat" wrote:
>
start
This
the
procedure
folder if
name
will

DTS - Excel file path from a table

SQL 2000
I got a table with file names and path for the Excel files to be loaded into
a sql table.
My plan is to use DTS and global variable to assign the path and file name
and use that as the source for the data-pump.
Any thoughts or sample code to achieve this '
Thanks In Advance
JeffYou would probably want to have a look at these two articles then.
Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
> SQL 2000
> I got a table with file names and path for the Excel files to be loaded
> into a sql table.
> My plan is to use DTS and global variable to assign the path and file name
> and use that as the source for the data-pump.
> Any thoughts or sample code to achieve this '
> Thanks In Advance
> Jeff
>|||Thanks Allan
My Excel sheet contains different sheets and I just want to read one sheet
to load into table. Thoughts/Ideas '
Thanks
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
> You would probably want to have a look at these two articles then.
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
> How to loop through a global variable Rowset
> (http://www.sqldts.com/default.aspx?298)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>|||The worksheets/named ranges within a workbook will be exposed to DTS as
table objects.
So this will give you the ability to change the DataSource property of the
Excel workbook and the SourceObjectName of the DataPump task.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan
> My Excel sheet contains different sheets and I just want to read one sheet
> to load into table. Thoughts/Ideas '
> Thanks
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>> You would probably want to have a look at these two articles then.
>> Looping, Importing and Archiving
>> (http://www.sqldts.com/default.aspx?246)
>> How to loop through a global variable Rowset
>> (http://www.sqldts.com/default.aspx?298)
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>> "MS User" <sqlman@.sql.com> wrote in message
>> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>>
>|||Thanks Allan. That did it.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23B%23e8JA5EHA.828@.TK2MSFTNGP14.phx.gbl...
> The worksheets/named ranges within a workbook will be exposed to DTS as
> table objects.
> So this will give you the ability to change the DataSource property of the
> Excel workbook and the SourceObjectName of the DataPump task.
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> Thanks Allan
>> My Excel sheet contains different sheets and I just want to read one
>> sheet to load into table. Thoughts/Ideas '
>> Thanks
>>
>> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
>> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>> You would probably want to have a look at these two articles then.
>> Looping, Importing and Archiving
>> (http://www.sqldts.com/default.aspx?246)
>> How to loop through a global variable Rowset
>> (http://www.sqldts.com/default.aspx?298)
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>> "MS User" <sqlman@.sql.com> wrote in message
>> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>>
>>
>

DTS - Excel file path from a table

SQL 2000
I got a table with file names and path for the Excel files to be loaded into
a sql table.
My plan is to use DTS and global variable to assign the path and file name
and use that as the source for the data-pump.
Any thoughts or sample code to achieve this ?
Thanks In Advance
Jeff
You would probably want to have a look at these two articles then.
Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
> SQL 2000
> I got a table with file names and path for the Excel files to be loaded
> into a sql table.
> My plan is to use DTS and global variable to assign the path and file name
> and use that as the source for the data-pump.
> Any thoughts or sample code to achieve this ?
> Thanks In Advance
> Jeff
>
|||Thanks Allan
My Excel sheet contains different sheets and I just want to read one sheet
to load into table. Thoughts/Ideas ?
Thanks
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
> You would probably want to have a look at these two articles then.
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
> How to loop through a global variable Rowset
> (http://www.sqldts.com/default.aspx?298)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>
|||The worksheets/named ranges within a workbook will be exposed to DTS as
table objects.
So this will give you the ability to change the DataSource property of the
Excel workbook and the SourceObjectName of the DataPump task.

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan
> My Excel sheet contains different sheets and I just want to read one sheet
> to load into table. Thoughts/Ideas ?
> Thanks
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks Allan. That did it.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23B%23e8JA5EHA.828@.TK2MSFTNGP14.phx.gbl...
> The worksheets/named ranges within a workbook will be exposed to DTS as
> table objects.
> So this will give you the ability to change the DataSource property of the
> Excel workbook and the SourceObjectName of the DataPump task.
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
>