Time stamping a file name using a DTS

[Modica82]

Hi all,

I have created a simple DTS package using the DTS wizard within SQL Server 2000. The package simply copies the contents of a table and pumps them out to a Excel file. My problem is the file it outputs to has a set name. Is it possible to append the date that the file was created in front of the file name

e.g. 24/01/2005 - output.xls

If this possible within what i have already done or do i need to add another process. I apologise for asking a dim question but this is one of the first times i have used DTS, i was steered away from it other times.

Regards,

Rob

 

[jhall01]

First off...the only "dim" question, is the question that isn't asked.


Yes, this is possible but will take a little activeX scripting to dynamically create the filename:


To dynamically change the name use the following:

set pkg = DTSGlobalVariables.Parent
set destcon = pkg.Connections("destinationTaskname")

destFileLoc = Day(now())&"-"&Month(now())&"-"&year(now())& " - output.xls"

destcon.Datasource = destFileLoc

You can find help with this at

http://www.sqldts.com

Jon

 

[jhall01]

edit:

destFileLoc = "c:\yourroot\folders\" & Day(now())&"-"&Month(now())&"-"&year(now())& " - output.xls"

Forgot to tell it where to point. This directory needs to be a local directory on the Server when running as a job or needs to be a \\share\drive\ if the output destination is on another server.

Jon

 

[Modica82]

Thanks jhall01,

The code works like a dream. And thanks for the website. I have been looking for a half descent web resource to aid my learning in DTS!

Thanks again!

Rob