It is a common scenario to have a scheduled package that imports a new file
each day. Conversely you may wish to produce a uniquely named file each day.
You can change the filename (DataSource) property of a connection at run-time from within an ActiveX Script
In the following example we derive the filename based on the current date.
The file path is retrieved from a global variable and this combination of path and
name is used to update the connection "Text File (Source)".
' Pkg 200
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
If you are using SQL Server 2000 then a similar approach can be taken using Dynamic Properties Task.