Import Files with Date-stamped Filenames or Paths

Monday Mar 10th 2003 by DatabaseJournal.com Staff
Share:

The ActiveX feature of DTS gives us an added advantage in handling many situations. Follow along with MAK as he walks us through three of these situations.

MAK [Muthusamy Anantha Kumar]

DTS packages are fantastic features available in SQL Server 7.0 and SQL server 2000. One outstanding feature in the DTS package is the ability to run ActiveX scripts. ActiveX gives us an added advantage in handling many situations; we will discuss how to handle three of these situations.

  1. File-names with date-stamp as a prefix or suffix or root word.

    Eg: \\FileServer\SharedFiles\Bankthree-20030218.txt

  2. File-paths with date-stamp as a prefix or suffix or root word.

    Eg: \\FileServer\SharedFiles\20030218\BankFour.txt

  3. Handling only previous business day date-stamped files

Process Involved

Create Connection to Source files and database.

Give the source file names of SourceFile1 and SourceFile2 to any existing files, for example c:\autoexec.bat. We are going to control the Path of the connections through ActiveX Controls.

Create Transformation between Sourcefiles and Database connections.

Now create two ActiveX scripts, one for each sourcefile.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved