DTS Output file name

[Gweener]

Hi All,

Doing a simple DTS to execute a SQL task and then output the results to a text file.

Can do all this OK - EXCEPT, this is to be a daily task and I want to be able to control the naming convention of the file i.e. to have a different name each time it executes (integrate the date for example).

Can anyone advice on the best way to achieve?

Many thanks

 

[MartinCroft]

Hi

not sure how you can have a different text file name using a text file destination within DTS, but one option is to encode what you want into a stored procedure and then exec the stored procedure from the DTS. you could create a unique filename by encoding the date into the filename and using bcp to export out the data, building it up dynamically
or you could DTS the file out as your currently doing and keep the same name, then call a stored proc which will rename the file to a timestamped one, might be easier than the bcp option, use xp_cmdshell and then dos rename command to do the renaming

Mart

 

[mutley1]

As mart says, the best is probably to run the package, but make the last step an xp_cmdshell step, so if the output is normally blahblah.txt, then use xpcmdshell and have this as the cmd:

move blahblah.txt blahblah%date:/=%.txt

Should put the date on the end, e.g. blahblah02022006.txt