Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
How can I change the filename for a text file connection?
By Darren Green

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 Task.

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
Option Explicit

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

If you are using SQL Server 2000 then a similar approach can be taken using Dynamic Properties Task.