Import Files with Date-stamped Filenames or Paths - Page 2

Source Code of Sourcefile1path

'************************
'  Visual Basic ActiveX Script
'************************
Function Main()
Dim oConn, sFilename
'Business day calculation
mydate=now()-1
if datepart("w",mydate) = 7 then
mydate=mydate-1
end if
if datepart("w",mydate) = 1 then
mydate=mydate-2
end if

sFilename = "\\FileServer\SharedFiles\Bankthree-" &  Right(Year(mydate), 4)
If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename &  ".txt"
Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function

Note: The connection parameter is case sensitive. In this statement

'Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile1")'

'SourceFile1' is case sensitive.

Source Code of Sourcefile2path

'************************
'  Visual Basic ActiveX Script
'************************
Function Main()
        Dim oConn, sFilename
mydate=now()-1
	if datepart("w",mydate) = 7 then
	mydate=mydate-1
	end if
	if datepart("w",mydate) = 1 then
	mydate=mydate-2
	end if

sFilename = "\\FileServer\SharedFiles\" &  Right(Year(mydate), 4)
If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & Day(Mydate) Else sFilename = sFilename & Day(mydate)
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename &  "\BankFour.txt"
Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile2")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function

Note: The connection parameter is case sensitive. In this statement

'Set oConn = DTSGlobalVariables.Parent.Connections("SourceFile2")'

'SourceFile2' is case sensitive.

Now create a 'success' workflow between the ActiveX script and the source file paths.

If today's date is March 3, 2003 then, when you execute the ActiveX script SourceFile1Path, the path in the connection SourceFile1 will automatically be changed to the new path "\\FileServer\SharedFiles\Bankthree-20030228.txt." When you execute the ActiveX script SourceFile2Path the path in the connection SourceFile2 will automatically be changed to the new path "\\FileServer\SharedFiles\20030228\BankFour.txt"

Now update the transformation and execute the package.

Conclusion:

Every time when you execute the package, the ActiveX scripts formulate the previous business date and prepare the full path of the source files. This script can be used for all kinds of source files like XL, .csv, .txt, .mdb etc.