Need help creating a DTS package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Need help creating a DTS package

Author

Topic

christinags
Starting Member

2 Posts

Posted - 2007-07-05 : 13:32:50

Hi, Just started using DTS and I need help with this scenario.
I have a stored proc that generates a result set. I want to create a DTS package that calls this stored proc and puts the result set into an excel file. Could anyone tell me the steps I would have to use to accomplish this.

Thanks!

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-08 : 16:10:24

try using execute sql task with as connection your sql server, use build query and put there the statement exec [your_sp] and choose as output destination file an excel file;-)

Need an SQLDB consultant?
check www.veeningsengineering.nl

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-08 : 16:12:47

for 2000 you should choose sql query task

Need an SQLDB consultant?
check www.veeningsengineering.nl

christinags
Starting Member

2 Posts

Posted - 2007-07-12 : 10:51:01

Thanks for the reply but the problem I'm facing is how to put the SP results into an excel/text file. The output of the proc is just a result set and at parse time the output fields are not known. So how do I specify the transformations to be used?

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-12 : 16:36:50

[put this in the transformation]
declare @table table(col1 varchar(50), col2 numeric , col3 int)
insert into @table
exec sp(arguments)
select * from @table

Need an SQLDB consultant?
check www.veeningsengineering.nl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources