If calling DTS from stor proc is it possible to change dest file name?

[Plato2]

I'm calling a DTS package from a stored procedure is it possible to change the file name of a destination file?

I'm transfering data from SQL db to Excel file...

Thanks in advance

 

[MDXer]

it depends on what method you are using to output your file to.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Plato2]

I created DTS package that moves data from SQL server to an Excel file. Then I call this DTS package from SQL server using the following:

EXEC master..XP_CMDSHELL "dtsrun /S Server_Name /N DTS_Name/E"

But I want to change the destination file name. Is there a way to do that?

Thanks in advance

 

[JohnDTampaBay]

Can you derive the name of the file from a simple query?
Even if its something like
SELECT 'TextFile'+Convert(char(8),GetDate(),112)+'.TXT'

If so, you can use a Dynamic Properties task within the DTS package to query the database and set the destination filename. If the query is complex or is derived from the calling procedure, then there is another more complex route.

How does the Dynamic Properties task work for you? Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Plato2]

Thanks DTS works fine for me. I didn't know about this feature in SQL 2000.

 

[Plato2]

I'm trying to change the destination file name using select statement but I cannot figure out how to do that...

 

[Glowworm27]

Check out this thread

thread961-872156

This person wants to do something similar, If you follow the same general Idea, you could set the Dest File name via, Global variable which you would pass in to the DTS package when you execute it from the Stored procedure that is described in the thread

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips