I'm using DTS to export data to a csv file. This will be scheduled to
create a new file every day.

How do I name each file with today's date?

eg: 01012002.csv, 02012003.csv etc?

Thanks
Leslie

Hi Leslie,
You need to use activeX script task to to this.
Regards.
JFB

Here is an example:

Function Main()
Dim oConn, sFilename

' Filename format - exyymmdd.log
sFilename = "ex" & Right(Year(Now()), 2)
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename & ".log"

Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename

Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function


<les> wrote

Thanks JFB.

I've only got as far as a simple DTS package that connects to a
database then copies some data to a csv file. I have tried adding an
activex script task based on your example to run on completion of the
text file(destination) connection, but I don't know how to configure
it.

Do I have to create DTSGlobalVariables?
Is Logfilepath the path to my csv file? eg: C:\folder\myfile.csv
What is: DTSGlobalVariables.Parent.Connections("Text File (Source)")

Leslie


On Fri, 1 Aug 2003 17:04:48 -0400, "JFB" <jfb2002 (AT) directvinternet (DOT) com>
wrote:

In article <j81mivo6ststoqmslr1ul12pqjn9pll2so (AT) 4ax (DOT) com>, les@?.?.invalid
writes
DTSGlobalVariables is an implicit handle to the package GlobalVariables
collection.

In that example it expects you to have a global variable called
LogFilePath which holds the file path. The file name is derived in code,
the statements above the global variable reference.

DTSGlobalVariables.Parent.Connections("Text File (Source)") is a
reference to your text file connection.

DTSGLobalVariables gives us the packages global variables collection.
Parent is the parent of the global variables collection, so this gives
us the package.
Connections is a reference to package's Connections collection, and you
are referencing the connection named Text File (Source). You may need to
change this to match the name of the text file connection in your
package.

That section could also be written like this-

' Get current package
Set oPkg = DTSGlobalVariables.Parent

'Get Connections collection
Set oConns = oPkg.Connections

' Get my text file Connection by name
Set oConn = oConns("Text File (Source)")

' Set the DataSource (filename) property of my connection
oConn.DataSource = "C:\Fred.txt"


Some similar resources-

How can I change the filename for a text file connection?
http://www.sqldts.com/default.aspx?6,101,200,0,1

Working with files and the FileSystemObject
http://www.sqldts.com/default.aspx?6,101,292,0,1

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Hi Leslie,
If you create a xls file in you DTS pkg you should have this:
1.Create table
2.Connection1 (odbc)
3.Connection 2 (excel file)
Create a ActiveX script task ( 4. ActiveX script task)
Now you should refer this line to you excel file name.
DTSGlobalVariables.Parent.Connections("Connection 2")
You dont need to create a Global Variable, this line only gets the
connection name into you active x script variable oConn.
I hope this is clear for you.
JFB

<les> wrote

Thanks both of you. I have it working now with:

Function Main()
Dim oConn, sFilename

' Filename format - exyymmdd.log
sFilename = "ST" & Right(Year(Now()), 2)
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = DTSGlobalVariables("csvpath").Value & _
sFilename & ".csv"

Set oConn = DTSGlobalVariables.Parent.Connections("Text File
(Destination)")
oConn.DataSource = sFilename

Set oConn = Nothing

Main = DTSTaskExecResult_Success
End Function


Leslie


On Sat, 2 Aug 2003 06:52:05 -0400, "JFB" <jfb2002 (AT) directvinternet (DOT) com>
wrote: